Tag: calculate business days between two dates in SQL Server

Calendar Table in SQL Server

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 (
		Select 
		         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,IsHoliday,HolidayDescription)
Select 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

Assumptions:

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.

Use Cases:

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!