Just a quick script to create a calendar dimension (aka date dimension) table for use in data warehouse queries.
Nothing too fancy here, but it does demonstrate some new functions in SQL Server 2012, including CONCAT, DATEFROMPARTS,and EOMONTH. It also shows a technique for using a CTE to generate a table of numbers, described by Itzik Ben-Gan in (among other places, I think), Inside Microsoft SQL Server 2008: TSQL Querying.
To set the date range to be generated, just change the dates in the CTE at the top of the script. Note that the second date you specify will be a day after the last day in the table, i.e. specifying 1 January 2021 will generate dates up to and including 31 December 2020.
You can use this script to SELECT…INTO a table, or to populate a previously created date dimension.
I decided to use a date column as the key, since that’s what all the cool kids do these days. There are also integer and varchar versions of the date column, just in case your application works better with these.
WITH DateRange (dateKey)
AS (
SELECT DATEFROMPARTS(2000, 01, 01) AS dateKey
UNION ALL
SELECT DATEADD(dd, 1, dateKey) AS dateKey
FROM DateRange
WHERE datekey < DATEFROMPARTS(2001, 01, 01)
)
SELECT dateKey
,YEAR(dateKey) * 10000 + MONTH(dateKey) * 100 + DAY(DateKey) AS dateKeyAsInt
,CAST(dateKey AS DATETIME) AS dateKeyAsDateTime
,CONVERT(CHAR(8), dateKey, 112) AS DateKeyAsChar
,CONVERT(VARCHAR(10), dateKey, 103) AS UKShortDate
,CONVERT(VARCHAR(20), dateKey, 106) AS UKDATENAME
,CONCAT (
DAY(dateKey)
,' '
,DATENAME(month, dateKey)
,' '
,YEAR(dateKey)
) AS UKLongDate
,YEAR(dateKey) AS YEAR
,Month(dateKey) AS MonthNumberInYEAR
,DATENAME(month, dateKey) AS MonthName
,DAY(dateKey) AS DAYNumberInMonth
,DATENAME(dy, dateKey) AS DAYNumberInYEAR
,DATENAME(dw, dateKey) AS DAYName
,CASE
WHEN LEFT(DATENAME(dw, dateKey), 1) = 'S'
THEN 1
ELSE 0
END AS IsWeekend
,CASE
WHEN dateKey = EOMONTH(dateKey)
THEN 1
ELSE 0
END AS IsMonthEnd
,DATEPART(quarter, dateKey) AS CalendarQuarter
,CONCAT (
'Q'
,DATEPART(quarter, dateKey)
) AS CalendarQuarterName
,CONCAT (
'Q'
,DATEPART(quarter, dateKey)
,' '
,YEAR(dateKey)
) AS CalendarQuarterAndYEAR
,CONCAT (
DATENAME(month, dateKey)
,' '
,YEAR(dateKey)
) AS MonthNameAndYEAR
,STUFF(YEAR(DateKey), 1, 2, CONCAT (
LEFT(DATENAME(MONTH, dateKey), 3)
,'-'
)) AS ShortMonthNameAndYEAR
,DATENAME(ISO_WEEK, dateKey) AS ISOWeek
FROM Daterange
OPTION (MAXRECURSION 0)