Creating a Custom Date Dimension Table

A date dimension is a common requirement in reporting systems. The inclusion of time in the SQL datetime datatype can make this type difficult to work with. How for example would you group by whole-day using a datetime?

Assigning a unique integer to each day and joining to a "date dimension" table simplifies date based reporting.

The table below is one I have used on many reporting projects. It contains many columns useful for labeling dates in a report. In OLAP solutions the date dimension is usually represented as a multi-level hierarchy containing year, month, day. Other hierarchies are possible like Year, Quarter, Week, Day.

Noteworthy issues are:

Weeks do not mix well with months. In a single dimension you must chose between months and weeks. The first and last week of a year are partial weeks; similarly if Quarter is the parent dimension the first and last week of the quarter will be partial weeks.

Most columns in this table have both an integer form and a string form. The integer value is preferable for use in aggregate operations (group by ) the textual form is preferable as a label in reports and Cubes. Reporting tools often allow you to SORT on the label, therefore it is important that the alphanumeric sort order matches the integer key sort order. Notice in the example data that many labels start with a two character number 01, 02, 03, etc.

Example Year, Month, Day date dimension.

Example Table Data.

Table Definition

#	Name	Datatype	Nullable	Comment
1	day_id	int	not null	0 = Jan 1 2000 (or the date of your choice) select datediff( day, '1/1/2000', your-date )"
2	weekday_id	tinyint	not null	1.. 7 1 = Sunday, 2=Monday, 7=Saturday.
3	weekday	char(9)	not null	Monday, Tuesday, Wednesday
4	month_id	tinyint	not null	1..12 1=Jan, 12=Dec
5	mmm	char(3)	not null	Jan, Feb, Mar, Apr, May
6	month	char(14)	not null	January February
7	year	smallint	not null	Numeric year value
8	day	tinyint	not null	1..n day of month. Numeric.
9	dd	char(2)	null	Two character day of month 01, 02, 03, ... 10, 11 Alpha sort order matches numeric
10	ddd	varchar(7)	null	Mon, Tue, Wed
11	week	char(2)	null	Week of year 01, 02, 03, ...51,52
12	week_id	tinyint	not null	Numeric week of year.
13	week_str	char(15)	not null	Starting date of week week_id + mmm + dd 01 Jan 01 02 Jan 07
14	Qtr	varchar(2)	not null	Q1, Q2, Q3, Q4
15	period_id	int	not null	Number of months since Jan 1 2000. datediff(month, '1/1/2000', your-date) 0 = Jan 2000 1 = Feb 2000 12 = Jan 2002 24 = Jan 2003 This integer uniquely identifies each month. Like the volume number on a magazine subscription. It is a useful construct in accounting databases.

Populating the dim_date table.

6insert into dim_date(

7 day_id

8 ,weekday_id

9 ,weekday

10 ,month_id

11 ,mmm

12 ,month

13 ,year

14 ,day

15 ,dd

16 ,ddd

17 ,week

18 ,week_id

19 ,week_str

20 ,Qtr

21 ,period_id

22)

23exec admin_fabricate_dim_date '12/26/2009', '1/1/2020'

A stored procedure to populate the table [dbo].[dim_date].

CREATE procedure admin_fabricate_dim_date(

@start_date datetime

,@end_date datetime

) as

select

day_id

,weekday_id

,weekday = case weekday_id when 1 then 'Sunday'

when 2 then 'Monday'

when 3 then 'Tuesday'

when 4 then 'Wednesday'

when 5 then 'Thursday'

when 6 then 'Friday'

when 7 then 'Saturday'

end

,month_id

,mmm = case datepart(month, ddate) when 1 then 'Jan'

when 2 then 'Feb'

when 3 then 'Mar'

when 4 then 'Apr'

when 5 then 'May'

when 6 then 'Jun'

when 7 then 'Jul'

when 8 then 'Aug'

when 9 then 'Spt'

when 10 then 'Oct'

when 11 then 'Nov'

when 12 then 'Dec'

end

,month = case month_id when 1 then '01 January'

when 2 then '02 February'

when 3 then '03 March'

when 4 then '04 April'

when 5 then '05 May'

when 6 then '06 June'

when 7 then '07 July'

when 8 then '08 August'

when 9 then '09 September'

when 10 then '10 October'

when 11 then '11 November'

when 12 then '12 December'

end

,[year]

,[day]

,dd = case when [day]<10 then '0'+ cast([day] as char(1))

else cast([day] as char(2))

end

,ddd = case when [day]<10 then '0'+ cast([day] as char(1))

else cast([day] as char(2))

end

+' '

+ case [weekday_id] when 1 then 'Sun'

when 2 then 'Mon'

when 3 then 'Tue'

when 4 then 'Wed'

when 5 then 'Thr'

when 6 then 'Fri'

when 7 then 'Sat'

end

,week = case when week_id < 10 then '0'+cast(week_id as char(1))

else cast(week_id as char(2))

end

,week_id

,week_str = case when week_id < 10 then '0'+cast(week_id as char(1))

else cast(week_id as char(2))

end

+' '

+ case month_id when 1 then 'Jan'

when 2 then 'Feb'

when 3 then 'Mar'

when 4 then 'Apr'

when 5 then 'May'

when 6 then 'Jun'

when 7 then 'Jul'

when 8 then 'Aug'

when 9 then 'Spt'

when 10 then 'Oct'

when 11 then 'Nov'

when 12 then 'Dec'

end

+' '

+ case when first_day_of_week<10 then '0'+cast(first_day_of_week as char(1))

else cast(first_day_of_week as char(2))

end

,Qtr = case quarter_id when 1 then 'Q1'

when 2 then 'Q2'

when 3 then 'Q3'

when 4 then 'Q4'

end

,period_id

from (

select

day_id

,ddate

,first_day_of_week = datepart(day, dateadd(day, (-1 * (datepart(weekday,ddate)-1)), ddate))

,week_id = datepart( week, ddate)

,weekday_id = datepart(weekday, ddate)

,month_id = datepart(month, ddate)

,[year] = datepart(year, ddate)

,[day] = datepart( day, ddate)

,quarter_id = datepart(quarter,ddate)

,period_id = datediff( month, '1/1/2000', ddate )

from (

select i as day_id

,dateadd(day, i, '1/1/2000') as ddate

from numberline

where i between datediff(day, '1/1/2000', @start_date)

and datediff(day, '1/1/2000', @end_date)

) as a

return