List Calendar Dates

Posted on Updated on

I use this to bounce against may weekly ETL’s to make sure that every day of the week data is captured. This helps me quickly identify a Calendar date with no ETL date which means no data was pulled.

DECLARE @start_date DATETIME= ‘2017-01-01’;
DECLARE @end_date DATETIME= GETDATE();
WITH CalendarDates
AS (
SELECT CAST(@start_date AS DATE) AS Calendar_Day
UNION ALL
SELECT DATEADD(DAY, 1, Calendar_Day)
FROM CalendarDates
WHERE Calendar_Day < @end_date)

SELECT Calendar_Day,
DATENAME(dw, Calendar_Day) AS Day_of_Week,
DATENAME(week, Calendar_Day) AS Week_Number
FROM CalendarDates OPTION(MAXRECURSION 0);

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s