Saturday, January 12, 2019

How to generate calendar in SQL Server?

How to generate calendar dates between two given dates. This post will show the query to create calendar table.

When there is any requirement to generate calendar you can generate programmatically and push it into a table.


CREATE TABLE CALENDAR(VDATE DATE)

DECLARE @MinDate DATE = '20181010',
        @MaxDate DATE = '20191010';

INSERT INTO CALENDAR
SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;



Tags: Generate dates between two dates ; calendar dates sql server ;sql calendar ; all dates;

No comments:

Post a Comment