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

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!

Be Sociable, Share!

• 
• 

Related posts:

1. How to Add a Date Filter to a BDC …
2. First and Last Day of Month Calculations in T-SQL …
3. SQL Server Spatial Data for Canada …
4. Refactor This: Runaway T-SQL to Print YYMMDD …
5. A More Effective Selective Index Rebuild/Reorganize Strategy …