Generate a date dimension in T-SQL

with date_gen as(

select cast(‘2005-01-01’ as datetime) d

union all

select d + 1

from date_gen

where d + 1 < ‘2079-01-01’

)

select d date_id, CONVERT(varchar, d,107) date_description,

YEAR(d) *100 + MONTH(d) date_year_month, YEAR(d) date_year,

MONTH(d) date_month, datename(month, d) date_month_name

from date_gen

OPTION (MAXRECURSION 0)

Leave a Reply

Your email address will not be published. Required fields are marked *