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?
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
How do I get Day name of first day of given month and year?
Select count(1) as 'No. of Business Days' From Calendar where Date between '2020-01-01' and '2020-12-31' and IsHoliday != 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!
Select Date,DayName FRom Calendar where YearNo=2020 and MonthNo =11 and DayNo=1
One thought on “Calendar Table in SQL Server”