SQL Server Portal

Quite often we need to generate a calendar table in our database for reporting purposes with a specific date range. We may generate it for a month, a quarter or a year depending upon our requirements. Since there was no proper function available to generate calendar table in earlier version of SQL Server (Earlier than 2022), we used DATEADD(), DATEDIFF() to achieve it somehow.

In SQL Server 2022, a new function came called GENERATE_SERIES(), which was a long awaited function. One of the usages of this function is that it can easily generate calendar table between two specific dates without using any additional functions.

Let me show you in the below example, how we can use GENERATE_SERIES() function to generate calendar table in SQL Server 2022.

Compatibility Level:

Your database compatibility level MUST be 160 or higher to use this function. Given below is the query to change your database compatibility level.

ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 160

Example :

CREATE TABLE tbl_Calendar
(
  [ID]             INT IDENTITY
, [Date]           DATE
, [Day]            INT
, [DayShortName]   VARCHAR(100)
, [DayName]        VARCHAR(100)
, [Week]           INT
, [MonthShortName] VARCHAR(100)
, [Month]          INT
, [MonthName]      VARCHAR(100)
, [Quarter]        INT
, [QuarterName]    VARCHAR(100)
, [Year]           INT
);
GO

DECLARE @SetStartDate DATE, @Start INT, @Stop INT, @Step INT;

SET @SetStartDate = '2022-01-01' --Start date of the calender;
SET @Start   = 0;
SET @Stop    = 365 -- No of days(one year) ;
SET @Step    = 1;

INSERT INTO tbl_Calendar
( 
  [Date]
, [Day]
, [DayShortName]
, [DayName]
, [Week]
, [MonthShortName]
, [Month]
, [MonthName]
, [Quarter]
, [QuarterName] 
, [Year]
)
SELECT DATEADD(DAY,value,@SetStartDate) AS [Date]
     , DATEPART(DAY,DATEADD(DAY,value,@SetStartDate)) AS [Day]
	 , FORMAT(DATEADD(DAY,value, @SetStartDate) ,'ddd') AS [DayShortName]
	 , DATENAME(WEEKDAY,DATEADD(WEEKDAY,value,@SetStartDate)) AS [DayName]
     , DATEPART(WEEK,DATEADD(DAY,value,@SetStartDate)) AS [Week]
	 , FORMAT(DATEADD(DAY,value, @SetStartDate) ,'MMM') AS [MonthShortName]
	 , MONTH(DATEADD(DAY,value,@SetStartDate)) AS [Month]
     , DATENAME(MONTH,DATEADD(DAY,value,@SetStartDate)) AS [MonthName]
	 , DATEPART(QUARTER,DATEADD(DAY,value,(@SetStartDate))) AS [Quarter]
	 , CONCAT('Quarter - ',DATEPART(QUARTER,DATEADD(DAY,value,(@SetStartDate)))) AS [QuarterName]
     , YEAR(DATEADD(DAY,value,@SetStartDate)) AS [Year]
FROM GENERATE_SERIES(@Start,@Stop,@Step) 
GO

SELECT * FROM tbl_Calendar;
GO

--OUTPUT

Conclusion:

I used GENERATE_SERIES() function to create calendar table and I noticed that it has reduced a lot of complexity in creating the calendar table, if we compare it with earlier approach. Do let me know, if you have generated calendar table and what was the approach.

Read Full Post »