Different T-SQL Date Variations for Date Dimensions

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 <code'Q' + CAST(DATEPART(qq, @dt) AS CHAR(1)) 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:

[sql]

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

[/sql]

Have fun!

Advertisements

4 comments

Leave a Reply

Fill in your details below or click an icon to log in:

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