What is Calendar Table in SQL Server ?
Calendar Table is a custom/user table in SQL Server created for ease of users coding. At times, it is observed many programmers are struggling to write date/calendar queries. Also, it is observed online processing of these functionalities has its own performance impact. As mentioned, Calendar table would come in time to save programmers as those are pre populated and quite easy to use as well.
Calendar Table Code
Drop Table if exists Calendar;
Create Table Calendar
Date Date Primary Key, DayNo TinyInt, MonthNo TinyInt, YearNo SmallInt
, WeekNo TinyInt, ISOWeekNo TinyInt,WeekDayNo TinyInt
, DayName Varchar(10), MonthName Varchar(10), QuaterNo TinyInt
, DayOfYearNo SmallInt, IsWeekEnd TinyInt
, IsHoliday TinyInt, HolidayDescription nVarchar(500)
;with cteSeed as ( Select 0 Seed union all Select Seed +1 From cteSeed where Seed <=100000)
, cteDateDimenion as ( Select dateadd(Day,Seed,cast('1990-01-01' as date) ) Date from cteSeed)
, cteCalendar as (
Date,Day(Date)DayNo,Month(Date) MonthNo, Year(Date) YearNo
,DAtePArt(Week,Date) WeeKNo, DATEPART(ISO_WEEK,Date) ISOWeekNo,DATEPART(WEEKDAY,Date) WeekDayNo
,DAteName(WEEKDAY,Date) DayName, DAteName(Month,Date) MonthName
,DATEPART(Quarter,date) QuaterNo, DATEPART(DAYOFYEAR, date) DayOfYearNo
,Case when DAteName(WEEKDAY,Date) in ('Saturday','Sunday') Then 1 Else 0 End 'IsWeekEnd'
,Case When Day(Date)=1 and Month(Date)=1 Then '|New Year Day|'
When Day(Date)=25 and Month(Date)=12 Then '|Christmas Day|'
Else '' End
+ Case when DAteName(WEEKDAY,Date) in ('Saturday','Sunday') Then '|'
+ DAteName(WEEKDAY,Date) + '|' Else '' End as HoliDayDescription
From cteDateDimenion )
Insert into Calendar (Date,DayNo,MonthNo,YearNo,WeekNo,ISOWeekNo,WeekDAyNo
,DayName,MonthName,QuaterNo,DayOfYearNo,IsWeekEnd,NULL,HolidayDescription From cteCalendar
OPTION (MAXRECURSION 0);
Update Calendar Set IsHoliday = Case when Len(HoliDayDescription) > 0 Then 1 Else 0 End
Select * From Calendar
1. The week day starts from Sunday.
2. Holiday list is specific to country, so the code above is as an example, feel free to add your specific holidays.
How do you calculate business/working days between two dates?
Select count(1) as 'No. of Business Days' From Calendar
where Date between '2020-01-01' and '2020-12-31' and IsHoliday != 1
How do I get Day name of first day of given month and year?
Select Date,DayName FRom Calendar where YearNo=2020 and MonthNo =11 and DayNo=1
I am sure, there many other use cases it would be helpful in real time, keep explore and feel free to make modification as required.
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!