Just another handy code snippet for generating dates for date dimensions in your data warehouse.

I am providing just the select statement here, but to generate (massive) date records, just create and set your start and end date variables, and enclose your insert and the code below in your WHILE loop.

 

Here’s a table summary

Date Variation T-SQL Code Sample Result
Date Surrogate Key CONVERT(VARCHAR(8), @dt, 112) 20110210
WeekName 'Week ' + CAST(DATEPART(ww, @dt) AS VARCHAR(2)) Week 7
WeekNameWithYear 'Week ' + CAST(DATEPART(ww, @dt) AS VARCHAR(2)) + ', ' +
CAST(YEAR(@dt) AS VARCHAR(4))
Week 7, 2011
WeekShortName 'WK' + RIGHT('0' + CAST(DATEPART(ww, @dt) AS VARCHAR(2)), 2) WK07
WeekShortNameWithYear 'WK' + RIGHT('0' + CAST(DATEPART(ww, @dt) AS VARCHAR(2)), 2) + ' ' +
CAST(YEAR(@dt) AS VARCHAR(4))
WK07 2011
WeekNumber DATEPART(ww, @dt) 7
FirstDateOfWeek DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww,0,@dt), 0)) 2011-02-06 00:00:00.000
LastDateOfWeek DATEADD(dd, 5, DATEADD(ww, DATEDIFF(ww,0,@dt), 0)) 2011-02-12 00:00:00.000
DayOfWeek DATEPART(dw, @dt) 5
DayOfWeekName DATENAME(dw, @dt) Thursday
DayOfWeekShortName LEFT(DATENAME(dw, @dt),3) Thu
IsWeekday CASE
WHEN DATENAME(dw, @dt) IN ('Saturday', 'Sunday') THEN 0
ELSE 1
END
1
IsWeekend CASE
WHEN DATENAME(dw, @dt) IN ('Saturday', 'Sunday') THEN 1
ELSE 0
END
0
MonthName DATENAME(mm, @dt) February
MonthNameWithYear DATENAME(mm, @dt) + ', ' + CAST(YEAR(@dt) AS CHAR(4)) February, 2011
MonthShortName LEFT(DATENAME(mm, @dt), 3) Feb
MonthShortNameWithYear LEFT(DATENAME(mm, @dt), 3) + ' ' + CAST(YEAR(@dt) AS CHAR(4)) Feb 2011
MonthNumber MONTH(@dt) 2
FirstDateOfMonth DATEADD(mm, DATEDIFF(mm, 0, @dt), 0) 2011-02-01 00:00:00.000
LastDateOfMonth DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, @dt) + 1, 0)) 2011-02-28 00:00:00.000
DayOfMonth DAY(@dt) 10
DayOfMonthName DATENAME(mm, @dt) + ' ' + CAST(DAY(@dt) AS VARCHAR(2)) +
CASE
WHEN DAY(@dt) IN (1, 21, 31) THEN 'st'
WHEN DAY(@dt) IN (2, 22) THEN 'nd'
WHEN DAY(@dt) IN (3, 23) THEN 'rd'
ELSE 'th'
END
February 10th
QuarterName 'Quarter ' + DATENAME(qq, @dt) Quarter 1
QuarterNameWithYear 'Quarter ' + CAST(DATEPART(qq, @dt) AS CHAR(1)) + ', ' + CAST(YEAR(@dt) AS CHAR(4)) Quarter 1, 2011
QuarterShortName Q1
QuarterShortNameWithYear 'Q' + CAST(DATEPART(qq, @dt) AS CHAR(1)) + ' ' + CAST(YEAR(@dt) AS CHAR(4)) Q1 2011
QuarterNumber DATEPART(qq, @dt) 1
FirstDateOfQuarter DATEADD(qq, DATEDIFF(qq, 0, @dt), 0) 2011-01-01 00:00:00.000
LastDateOfQuarter DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, @dt) + 1, 0)) 2011-03-31 00:00:00.000
DayOfQuarter DATEDIFF(dd, DATEADD(qq, DATEDIFF(qq, 0, @dt), 0), @dt) + 1 41
Year (YEAR(@dt)
FirstDateOfYear DATEADD(yy, DATEDIFF(yy,0, @dt), 0) 2011-01-01 00:00:00.000
LastDateOfYear DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy,0, @dt) + 1, 0)) 2011-12-31 00:00:00.000
DayOfYear DATEPART(dy,@dt) 41
DayOfYearName DATENAME(mm, @dt) + ' ' + CAST(DAY(@dt) AS VARCHAR(2)) +
CASE
WHEN DAY(@dt) IN (1, 21, 31) THEN 'st'
WHEN DAY(@dt) IN (2, 22) THEN 'nd'
WHEN DAY(@dt) IN (3, 23) THEN 'rd'
ELSE 'th'
END + ', ' + CAST(YEAR(@dt) AS CHAR(4))
February 10th, 2011

Here’s the code:

DECLARE @dt DATETIME = GETDATE()
-- insert your unknowns in your dimension first
SELECT
	--2011-02-10 01:18:55.247
	@dt AS CurrentDate,	
    -- 2011-02-10 00:00:00.000
    DATEADD(dd, 0, DATEDIFF(dd, 0, @dt)) AS DateAK, 
    -- 20110210
	CONVERT(VARCHAR(8), @dt, 112) AS DateSK, 
	-- Week 7
	'Week ' + CAST(DATEPART(ww, @dt) AS VARCHAR(2)) AS WeekName,
	-- Week 7, 2011
	'Week ' + CAST(DATEPART(ww, @dt) AS VARCHAR(2)) + ', ' + 
	 CAST(YEAR(@dt) AS VARCHAR(4)) AS WeekNameWithYear,
	-- WK07
	'WK' + RIGHT('0' + CAST(DATEPART(ww, @dt) AS VARCHAR(2)), 2) AS WeekShortName,
	-- WK07 2011	
	'WK' + RIGHT('0' + CAST(DATEPART(ww, @dt) AS VARCHAR(2)), 2) + ' ' + 
	CAST(YEAR(@dt) AS VARCHAR(4)) AS WeekShortNameWithYear,
	-- 7
	DATEPART(ww, @dt) AS WeekNumber,
	-- Sunday of the week - 2011-02-06 00:00:00.000
	DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww,0,@dt), 0)) AS FirstDateOfWeek,
	-- Saturday of the week - 2011-02-12 00:00:00.000
	DATEADD(dd, 5, DATEADD(ww, DATEDIFF(ww,0,@dt), 0)) AS LastDateOfWeek,
	-- 5
	DATEPART(dw, @dt) AS DayOfWeek,
	-- Thursday
	DATENAME(dw, @dt) AS DayOfWeekName,
	-- Thu
	LEFT(DATENAME(dw, @dt),3) AS DayOfWeekShortName,
	-- 1
	CASE 
	WHEN DATENAME(dw, @dt) IN ('Saturday', 'Sunday') THEN 0
	ELSE 1
	END AS IsWeekday,
	-- 0
	CASE 
	WHEN DATENAME(dw, @dt) IN ('Saturday', 'Sunday') THEN 1
	ELSE 0
	END AS IsWeekend,
	-- February
	DATENAME(mm, @dt) AS MonthName,
	-- February, 2011
	DATENAME(mm, @dt) + ', ' + CAST(YEAR(@dt) AS CHAR(4)) AS MonthNameWithYear,
	-- Feb
	LEFT(DATENAME(mm, @dt), 3) AS MonthShortName,
	-- Feb 2011
	LEFT(DATENAME(mm, @dt), 3) + ' ' + CAST(YEAR(@dt) AS CHAR(4))  AS MonthShortNameWithYear,
	-- 2
	MONTH(@dt) AS MonthNumber,
	-- 2011-02-01 00:00:00.000
	DATEADD(mm, DATEDIFF(mm, 0, @dt), 0) AS FirstDateOfMonth,
	-- 2011-02-28 00:00:00.000
	DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, @dt) + 1, 0)) AS LastDateOfMonth,
	-- 10
	DAY(@dt) AS DayOfMonth,
	-- February 10th
	DATENAME(mm, @dt) + ' ' + CAST(DAY(@dt) AS VARCHAR(2)) + 
	CASE 
		WHEN DAY(@dt) IN (1, 21, 31) THEN 'st' 
		WHEN DAY(@dt) IN (2, 22) THEN 'nd' 
		WHEN DAY(@dt) IN (3, 23) THEN 'rd'
		ELSE 'th'
	END AS DayOfMonthName, 
	-- Quarter 1
	'Quarter ' + DATENAME(qq, @dt) AS QuarterName,
	-- Quarter 1, 2011
	'Quarter ' + CAST(DATEPART(qq, @dt) AS CHAR(1)) + ', ' + CAST(YEAR(@dt) AS CHAR(4)) AS QuarterNameWithYear,
	-- Q1	
	'Q' + CAST(DATEPART(qq, @dt) AS CHAR(1)) AS QuarterShortName,
	-- Q1 2011
	'Q' + CAST(DATEPART(qq, @dt) AS CHAR(1)) + ' ' + CAST(YEAR(@dt) AS CHAR(4)) AS QuarterShortNameWithYear,
	-- 1	
	DATEPART(qq, @dt) AS QuarterNumber,
	-- 2011-01-01 00:00:00.000
	DATEADD(qq, DATEDIFF(qq, 0, @dt), 0) AS FirstDateOfQuarter,
	-- 2011-03-31 00:00:00.000
	DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, @dt) + 1, 0)) AS LastDateOfQuarter,
	-- 41	
	DATEDIFF(dd, DATEADD(qq, DATEDIFF(qq, 0, @dt), 0), @dt) + 1 AS DayOfQuarter,
	-- 2011
	CAST(YEAR(@dt) AS CHAR(4)) AS YearName,
	-- 11
	RIGHT(CAST(YEAR(@dt) AS CHAR(4)), 2) AS YearShortName,
	-- 2011
	YEAR(@dt) AS YearNumber,
	-- 2011-01-01 00:00:00.000
	DATEADD(yy, DATEDIFF(yy,0, @dt), 0) AS FirstDateOfYear,
	-- 2011-12-31 00:00:00.000
	DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy,0, @dt) + 1, 0)) AS LastDateOfYear,
	-- 41
	DATEPART(dy,@dt) AS DayOfYear,
	-- February 10th, 2011
	DATENAME(mm, @dt) + ' ' + CAST(DAY(@dt) AS VARCHAR(2)) + 
	CASE 
		WHEN DAY(@dt) IN (1, 21, 31) THEN 'st' 
		WHEN DAY(@dt) IN (2, 22) THEN 'nd' 
		WHEN DAY(@dt) IN (3, 23) THEN 'rd'
		ELSE 'th'
	END + ', ' + CAST(YEAR(@dt) AS CHAR(4)) AS DayOfYearName

Have fun!

VN:F [1.9.22_1171]
Rating: 9.8/10 (11 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
Different T-SQL Date Variations for Date Dimensions, 9.8 out of 10 based on 11 ratings  
Be Sociable, Share!
  • Tweet