Date Table for PowerPivot, without a valid table on the Source (On the Fly Generation)

Ever wondered on how to get a table with dates for various needs in PowerPivot where the source does not contain any such table?. Here is a short T-SQL Code to generate the Table with Dates. Since it's a standard T-SQL code, you should be able to modify the columns returned as you need them for your solution.

1) Open PwoerPivot, Choose to import data from SQL Server as a source and choose any Server and DB as a target (SQL Server to process the T-SQL statements).

 

2) In 'Table Import Wizard', choose 'Write a Query that will Specify the data to Import'

3) Copy the T-SQL Code mentioned below as a SQL Statement

 

declare @fromdate date, @todate date

set @fromdate = '01-Jan-1950'
set @ToDate = '31-Dec-2030'

;With DateSequence( [Date] ) as
(
                Select @fromdate as [Date]
                               union all
                Select dateadd(day, 1, [Date])
                               from DateSequence
                               where Date < @todate
)

select
                               YEAR([date]) as Year,
                               Month([date]) as Month,
                               DAY([DATE]) as Day,
                               [date] as Date,
                               CASE Month([date])
                                               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 'Sep'
                                               WHEN 10 THEN 'Oct'
                                               WHEN 11 THEN 'Nov'
                                               WHEN 12 THEN 'Dec'
                               END as MonthShort,
          CASE Month([date])
                                               WHEN 1 THEN 'Q1'
                                               WHEN 2 THEN 'Q1'
                                               WHEN 3 THEN 'Q1'
                                               WHEN 4 THEN 'Q2'
                                               WHEN 5 THEN 'Q2'
                                               WHEN 6 THEN 'Q2'
                                               WHEN 7 THEN 'Q3'
                                               WHEN 8 THEN 'Q3'
                                               WHEN 9 THEN 'Q3'
                                               WHEN 10 THEN 'Q4'
                                               WHEN 11 THEN 'Q4'
                                               WHEN 12 THEN 'Q4'
                               END as Quarter,
                               DATENAME(MONTH,[Date]) as [MonthName],
                               DATEPART(DAYOFYEAR,[Date]) as DayInYear,
                               DATEPART( wk, [date])  as Weeknumber
from DateSequence order by [Date]
option (MaxRecursion 32767)

 

4) You are Done and your New Table called 'Date' should be one of the table in your PowerPivot to use as part of your solution as any other table from the source.

 -----------------------------------------------------------------

Additional columns as needed can be added to the T-SQL and it will be returned as additional columns in PowerPivot.

Appreciate if you could modify the code and post it back to this blog, along with the short description of the business scenario for the modification, for others to benefit from your hard work.