Query to Build Date Table

Query to build a date table.  Use it to create date table on fly in Power Pivot or Persist the results to a date dim table in your data mart.

It will run in SQL Server 2005 and higher.

WITH Dates AS

      (

            SELECT      CAST('20000101' AS DATE) AS [Date]

            UNION ALL

            SELECT      DATEADD(DAY, 1, [Date])

            FROM  Dates

            WHERE [Date] < '20251231'

      )

      SELECT      Date,

                  CONVERT(Varchar,Date,112) as DateID,

                  YEAR(Date) as Year,

                  DATEPART(Quarter,Date) as Quarter,

                  DATEPART(WEEK,Date) as Week,

                  DATEPART(WEEKDAY,Date) as DayofWeek,

                  MONTH(Date)as MonthNumber,

                  DateName(Month,Date) as MonthName,

                  DateName(WeekDay,Date) as DayOfWeekName,

                  Case WHEN DATEPART(WEEKDAY,Date) in (1,7) THEN 0 ELSE 1 END as WeekDayFlag

      FROM  Dates

      OPTION ( MAXRECURSION 10000 );