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.

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