Date and Time dimension creation and population T-SQL. This script will create the tables, populate the date and time, add holidays, indexes... and contains example snippets at the bottom of the file.
USE <database_name>;
--Create the tables
begin try
drop table dim_Date;
end try
begin catch
--DO NOTHING
end catch;
create table dbo.dim_Date(
--[ID] [int] IDENTITY(1,1) NOT NULL--Use this line if you just want an autoincrementing counter AND COMMENT BELOW LINE
ID int not null ,--TO MAKE THE ID THE YYYYMMDD FORMAT USE THIS LINE AND COMMENT ABOVE LINE.
[Date] datetime not null,
Day char(2) not null,
DaySuffix varchar(4) not null,
DayOfWeek varchar(9) not null,
DOWInMonth tinyint not null,
DayOfYear int not null,
WeekOfYear tinyint not null,
WeekOfMonth tinyint not null,
[Month] char(2) not null,
MonthName varchar(9) not null,
Quarter tinyint not null,
QuarterName varchar(6) not null,
[Year] char(4) not null,
StandardDate varchar(10) null,
HolidayText varchar(50) null
constraint PK_dim_Date primary key clustered(ID asc)
with(pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY])
on [PRIMARY];
go
set ansi_padding off;
begin try
drop table dim_Time;
end try
begin catch
--DO NOTHING
end catch;
create table dbo.dim_Time(ID int identity(1, 1)
not null,
[Time] char(8) not null,
Hour char(2) not null,
MilitaryHour char(2) not null,
Minute char(2) not null,
Second char(2) not null,
AmPm char(2) not null,
StandardTime char(11) null,
constraint PK_dim_Time primary key clustered(ID asc)
with(pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [PRIMARY])
on [PRIMARY];
go
set ansi_padding off;
--Populate Date dimension
print convert(varchar, getdate(), 113); --To see the exact run time.
TRUNCATE table dim_Date;
--IF YOU ARE USING THE YYYYMMDD format for the primary key then you need to comment out this line.
--DBCC CHECKIDENT (dim_Date, RESEED, 60000) --In case you need to add earlier dates later.
declare @tmpDOW table(DOW int,
Cntr int);--Table for counting DOW occurance in a month
insert into @tmpDOW(DOW,
Cntr)
values
(
1,
0);--Used in the loop below
insert into @tmpDOW(DOW,
Cntr)
values
(
2,
0);
insert into @tmpDOW(DOW,
Cntr)
values
(
3,
0);
insert into @tmpDOW(DOW,
Cntr)
values
(
4,
0);
insert into @tmpDOW(DOW,
Cntr)
values
(
5,
0);
insert into @tmpDOW(DOW,
Cntr)
values
(
6,
0);
insert into @tmpDOW(DOW,
Cntr)
values
(
7,
0);
declare @StartDate datetime,
@EndDate datetime,
@Date datetime,
@WDofMonth int,
@CurrentMonth int;
select @StartDate = '1/1/1900',
@EndDate = '1/1/2050' ,--Non inclusive. Stops on the day before this.
@CurrentMonth = 1; --Counter used in loop below.
select @Date = @StartDate;
while @Date < @EndDate
begin
if datepart(MONTH, @Date) <> @CurrentMonth
begin
select @CurrentMonth = datepart(MONTH, @Date);
update @tmpDOW
set Cntr = 0;
end;
update @tmpDOW
set Cntr = Cntr + 1
where DOW = datepart(DW, @DATE);
select @WDofMonth = Cntr
from @tmpDOW
where DOW = datepart(DW, @DATE);
insert into dim_Date(ID, --TO MAKE THE ID THE YYYYMMDD FORMAT UNCOMMENT THIS LINE... Comment for autoincrementing.
[Date],
Day,
DaySuffix,
DayOfWeek,
DOWInMonth,
DayOfYear,
WeekOfYear,
WeekOfMonth,
[Month],
MonthName,
Quarter,
QuarterName,
[Year])
select convert(varchar, @Date, 112), --TO MAKE THE ID THE YYYYMMDD FORMAT UNCOMMENT THIS LINE COMMENT FOR AUTOINCREMENT
@Date as [Date],
datepart(DAY, @DATE) as Day,
case
when datepart(DAY, @DATE) in(11, 12, 13)
then cast(datepart(DAY, @DATE) as varchar) + 'th'
when right(datepart(DAY, @DATE), 1) = 1
then cast(datepart(DAY, @DATE) as varchar) + 'st'
when right(datepart(DAY, @DATE), 1) = 2
then cast(datepart(DAY, @DATE) as varchar) + 'nd'
when right(datepart(DAY, @DATE), 1) = 3
then cast(datepart(DAY, @DATE) as varchar) + 'rd'
else cast(datepart(DAY, @DATE) as varchar) + 'th'
end as DaySuffix,
case datepart(DW, @DATE)
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 as DayOfWeek,
@WDofMonth as DOWInMonth ,--Occurance of this day in this month. If Third Monday then 3 and DOW would be Monday.
datepart(dy, @Date) as DayOfYear ,--Day of the year. 0 - 365/366
datepart(ww, @Date) as WeekOfYear ,--0-52/53
datepart(ww, @Date) + 1 - datepart(ww, cast(datepart(mm, @Date) as varchar) + '/1/' + cast(datepart(yy, @Date) as varchar)) as WeekOfMonth,
datepart(MONTH, @DATE) as [Month] ,--To be converted with leading zero later.
datename(MONTH, @DATE) as MonthName,
datepart(qq, @DATE) as Quarter ,--Calendar quarter
case datepart(qq, @DATE)
when 1
then 'First'
when 2
then 'Second'
when 3
then 'Third'
when 4
then 'Fourth'
end as QuarterName,
datepart(YEAR, @Date) as [Year];
select @Date = dateadd(dd, 1, @Date);
end;
--You can replace this code by editing the insert using my functions dbo.DBA_fnAddLeadingZeros
update dbo.dim_Date
set DAY = '0' + DAY
where len(DAY) = 1;
update dbo.dim_Date
set [MONTH] = '0' + [MONTH]
where len([MONTH]) = 1;
update dbo.dim_Date
set STANDARDDATE = [MONTH] + '/' + DAY + '/' + [YEAR];
--Add HOLIDAYS --------------------------------------------------------------------------------------------------------------
--THANKSGIVING --------------------------------------------------------------------------------------------------------------
--Fourth THURSDAY in November.
update DIM_DATE
set HolidayText = 'Thanksgiving Day'
where [MONTH] = 11
and DAYOFWEEK = 'Thursday'
and DOWInMonth = 4;
go
--CHRISTMAS -------------------------------------------------------------------------------------------
update dbo.dim_Date
set HolidayText = 'Christmas Day'
where [MONTH] = 12
and DAY = 25;
--4th of July ---------------------------------------------------------------------------------------------
update dbo.dim_Date
set HolidayText = 'Independance Day'
where [MONTH] = 7
and DAY = 4;
-- New Years Day ---------------------------------------------------------------------------------------------
update dbo.dim_Date
set HolidayText = 'New Year''s Day'
where [MONTH] = 1
and DAY = 1;
--Memorial Day ----------------------------------------------------------------------------------------
--Last Monday in May
update dbo.dim_Date
set HolidayText = 'Memorial Day'
from dim_Date
where ID in(select max(ID)
from dbo.dim_Date
where MonthName = 'May'
and DayOfWeek = 'Monday'
group by [YEAR],
[MONTH]);
--Labor Day -------------------------------------------------------------------------------------------
--First Monday in September
update dbo.dim_Date
set HolidayText = 'Labor Day'
from dim_Date
where ID in(select min(ID)
from dbo.dim_Date
where MonthName = 'September'
and DayOfWeek = 'Monday'
group by [YEAR],
[MONTH]);
-- Valentine's Day ---------------------------------------------------------------------------------------------
update dbo.dim_Date
set HolidayText = 'Valentine''s Day'
where [MONTH] = 2
and DAY = 14;
-- Saint Patrick's Day -----------------------------------------------------------------------------------------
update dbo.dim_Date
set HolidayText = 'Saint Patrick''s Day'
where [MONTH] = 3
and DAY = 17;
go
--Martin Luthor King Day ---------------------------------------------------------------------------------------
--Third Monday in January starting in 1983
update dim_Date
set HolidayText = 'Martin Luthor King Jr Day'
where [MONTH] = 1--January
and Dayofweek = 'Monday'
and [YEAR] >= 1983--When holiday was official
and DOWInMonth = 3;--Third X day of current month.
go
--President's Day ---------------------------------------------------------------------------------------
--Third Monday in February.
update dim_Date
set HolidayText = 'President''s Day'--select * from dim_date
where [MONTH] = 2--February
and Dayofweek = 'Monday'
and DOWInMonth = 3;--Third occurance of a monday in this month.
go
--Mother's Day ---------------------------------------------------------------------------------------
--Second Sunday of May
update dim_Date
set HolidayText = 'Mother''s Day'--select * from dim_date
where [MONTH] = 5--May
and Dayofweek = 'Sunday'
and DOWInMonth = 2;--Second occurance of a monday in this month.
go
--Father's Day ---------------------------------------------------------------------------------------
--Third Sunday of June
update dim_Date
set HolidayText = 'Father''s Day'--select * from dim_date
where [MONTH] = 6--June
and Dayofweek = 'Sunday'
and DOWInMonth = 3;--Third occurance of a monday in this month.
go
--Halloween 10/31 ----------------------------------------------------------------------------------
update dbo.dim_Date
set HolidayText = 'Halloween'
where [MONTH] = 10
and DAY = 31;
--Election Day--------------------------------------------------------------------------------------
--The first Tuesday after the first Monday in November.
begin try
drop table #tmpHoliday;
end try
begin catch
--do nothing
end catch;
create table #tmpHoliday(ID int identity(1, 1),
DateID int,
Week tinyint,
YEAR char(4),
DAY char(2));
insert into #tmpHoliday(DateID,
[YEAR],
DAY)
select id,
[YEAR],
DAY
from dbo.dim_Date
where [MONTH] = 11
and Dayofweek = 'Monday'
order by YEAR, DAY;
declare @CNTR int,
@POS int,
@STARTYEAR int,
@ENDYEAR int,
@CURRENTYEAR int,
@MINDAY int;
select @CURRENTYEAR = min([YEAR]),
@STARTYEAR = min([YEAR]),
@ENDYEAR = max([YEAR])
from #tmpHoliday;
while @CURRENTYEAR <= @ENDYEAR
begin
select @CNTR = count([YEAR])
from #tmpHoliday
where [YEAR] = @CURRENTYEAR;
set @POS = 1;
while @POS <= @CNTR
begin
select @MINDAY = min(DAY)
from #tmpHoliday
where [YEAR] = @CURRENTYEAR
and WEEK is null;
update #tmpHoliday
set WEEK = @POS
where [YEAR] = @CURRENTYEAR
and DAY = @MINDAY;
select @POS = @POS + 1;
end;
select @CURRENTYEAR = @CURRENTYEAR + 1;
end;
update DT
set HolidayText = 'Election Day'
from dbo.dim_Date DT
join #tmpHoliday HL
on HL.DateID + 1 = DT.ID
where WEEK = 1;
drop table #tmpHoliday;
go
--------------------------------------------------------------------------------------------------------
print convert(varchar, getdate(), 113);--USED FOR CHECKING RUN TIME.
--Load time data for every second of a day
declare @Time datetime;
set @TIME = convert(varchar, '12:00:00 AM', 108);
TRUNCATE table dim_Time;
while @TIME <= '11:59:59 PM'
begin
insert into dbo.dim_Time([Time],
Hour,
MilitaryHour,
Minute,
Second,
AmPm)
select convert(varchar, @TIME, 108) as [Time],
case
when datepart(HOUR, @Time) > 12
then datepart(HOUR, @Time) - 12
else datepart(HOUR, @Time)
end as Hour,
cast(substring(convert(varchar, @TIME, 108), 1, 2) as int) as MilitaryHour,
datepart(MINUTE, @Time) as Minute,
datepart(SECOND, @Time) as Second,
case
when datepart(HOUR, @Time) >= 12
then 'PM'
else 'AM'
end as AmPm;
select @TIME = dateadd(second, 1, @Time);
end;
update dim_Time
set HOUR = '0' + HOUR
where len(HOUR) = 1;
update dim_Time
set MINUTE = '0' + MINUTE
where len(MINUTE) = 1;
update dim_Time
set SECOND = '0' + SECOND
where len(SECOND) = 1;
update dim_Time
set MilitaryHour = '0' + MilitaryHour
where len(MilitaryHour) = 1;
update dim_Time
set StandardTime = Hour + ':' + Minute + ':' + Second + ' ' + AmPm
where StandardTime is null
and HOUR <> '00';
update dim_Time
set StandardTime = '12' + ':' + Minute + ':' + Second + ' ' + AmPm
where HOUR = '00';
--dim_date indexes---------------------------------------------------------------------------------------------
create unique nonclustered index IDX_dim_Date_Date on dbo.dim_Date([Date] asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Date_Day on dbo.dim_Date(Day asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Date_DayOfWeek on dbo.dim_Date(DayOfWeek asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Date_DOWInMonth on dbo.dim_Date(DOWInMonth asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Date_DayOfYear on dbo.dim_Date(DayOfYear asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Date_WeekOfYear on dbo.dim_Date(WeekOfYear asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Date_WeekOfMonth on dbo.dim_Date(WeekOfMonth asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Date_Month on dbo.dim_Date([Month] asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Date_MonthName on dbo.dim_Date(MonthName asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Date_Quarter on dbo.dim_Date(Quarter asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Date_QuarterName on dbo.dim_Date(QuarterName asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Date_Year on dbo.dim_Date([Year] asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Time_HolidayText on dbo.dim_Date(HolidayText asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
--dim_Time indexes
create unique nonclustered index IDX_dim_Time_Time on dbo.dim_Time([Time] asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Time_Hour on dbo.dim_Time(Hour asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Time_MilitaryHour on dbo.dim_Time(MilitaryHour asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Time_Minute on dbo.dim_Time(Minute asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Time_Second on dbo.dim_Time(Second asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Time_AmPm on dbo.dim_Time(AmPm asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
create nonclustered index IDX_dim_Time_StandardTime on dbo.dim_Time(StandardTime asc) with(pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY];
print convert(varchar, getdate(), 113);--USED FOR CHECKING RUN TIME.
-- ============================================================================
-- USAGE EXAMPLES
-- ============================================================================
--Join date and time and give all records for a specific date and time.
/*declare @DATETIME datetime; --select * from dim_date
set @DATETIME = '07/30/1976 4:01:02 PM';
select DiffDays = datediff(dd, [DATE], getdate()),
DiffYears = datediff(dd, [DATE], getdate()) / 365.242199,
*,
StandardDateTime = STANDARDDATE + ' ' + STANDARDTIME
from dim_Date as DT
join dim_Time as TM
on TM.TIME = convert(varchar, @DATETIME, 108)
where DATE = convert(varchar, @DATETIME, 101);*/
--GET MONTH AND YEAR WHERE IT HAS MORE THAN 4 FRIDAYS
/*select Month, Year, max(DOWInMonth)
from dim_Date
where DAYOFWEEK = 'FRIDAY'
and YEAR in(2008, 2009)
group by YEAR,
MONTH
having max(DOWInMonth) > 4
order by YEAR, MONTH;*/
--Get the number of days per year.
/*select YEAR, count(DAY) as days
from dim_Date
group by YEAR;*/
--Get paydays where 1/2/2009 is a payday and paydays are biweekly
/*select cast(datediff(dd, '1/2/2009', DATE) / 14.00 as varchar) as DiffFromStart, *
from dim_Date
where DAYOFWEEK = 'Friday'
and DATE >= '1/2/2001'--Starting at this date
and datediff(dd, '1/2/2009', DATE) / 14.0 = round((datediff(dd, '1/2/2009', DATE) / 14.0), 0);*/
--Month and year where we get three paydays in one month from 2009 on...
/*select MONTH, YEAR
from dim_Date
where DATE >= '1/2/2009'
and datediff(dd, '1/2/2009', DATE) / 14.0 = round((datediff(dd, '1/2/2009', DATE) / 14.0), 0)
group by MonthName, Month, Year
having count(DAY) >= 3
order by YEAR, MONTH;*/