Sqlserver007

SQL SERVER :Date and Time Dimensions in T-SQL using CTE

By Vivek on January 20th, 2010

Everytime we create a cube for analysis services Date and Time Dimensions are needed to slice and dice the historical Data. Sql Server Analysis Services uses the Time Intelligence Features to populate the date and time. Its posted in detail in this blog.

However we need to use them in our relational Databases to do some adhoc reporting. I always use the following CTE’s to create Date and Time Dimensions. The good thing about CTE is you can recursively loop through the data till you get the desired result.

DATE DIMENSION :

I got the Date Dimension idea originally from JamieT (Jamie Thomson,MVP SQL Server) ‘s blog.

with DateCTE  as
(
	select cast ('01-Jan-2009' as datetime)   Datevalue
	union all
	select datevalue + 1
	from DateCTE where datevalue + 1 < = '31-Dec-2020'
)
select cast(datevalue as int ) as DateID
,datename(year,DateValue) as [Year]
,dateName(Month,DateValue) as [Month]
,datename(d,datevalue) as [datenumber]
,datename(Week,Datevalue) as [Week]
,datename(DW,Datevalue) as [Day]
,Datevalue  as [Date]
from DateCTE
order by Datevalue
option (maxrecursion 0)

TIME DIMENSION :

  with HourCTE(Hour,Minute) as
  (
  select 0 as Hour , 0 as Minute
  union all
  select Hour , Minute + 1
  from HourCTE
  where Minute + 1 < 60
  ),
   MinuteCTE(Hour,minute) as
  (
    select Hour, Minute from HourCTE
	union all
	select Hour + 1 ,Minute from MinuteCTE
	where Hour + 1 < 24
)
select * from minuteCTE
order by Hour ,Minute