SQL Server Dates or Calendar Table for PowerPivot

http://www.wiseowl.co.uk/blog/s334/calendar.htm

SQL Server 2012

CREATE PROC spCreateCalendarTable(
@StartDate datetime = '20000101',
@EndDate datetime = '20201231'
) AS
 
-- create a table of dates for use in PowerPivot
 
-- NOT FOR COMMERCIAL USE OR REDISTRIBUTION
-- WITHOUT PRIOR WRITTEN PERMISSION FROM WISE OWL
 
-- get rid of any old versions of table
BEGIN TRY
DROP TABLE tblCalendar
END TRY
 
BEGIN CATCH
END CATCH
 
-- first create the table of dates
CREATE TABLE tblCalendar(
[Date] datetime PRIMARY KEY,
[Year] int,
MonthNumber int,
[MonthName] varchar(10),
MonthNameSorted varchar(20),
DayNumber int,
[DayName] varchar(10),
[Quarter] char(2)
)
 
-- now add one date at a time
DECLARE @i int = 0
DECLARE @curdate datetime = @StartDate
 
WHILE @curdate <= @EndDate
BEGIN
 
-- add a record for this date (could use FORMAT
-- function if SQL Server 2012 or later)
INSERT INTO tblCalendar (
[Date],
[Year],
MonthNumber,
[MonthName],
MonthNameSorted,
DayNumber,
[DayName],
[Quarter]
) VALUES (
@curdate,
Year(@curdate),
Month(@curdate),
DateName(m,@curdate),
 
-- get month name as eg "01 January" or "11 November"
CASE
WHEN month(@curdate) < 10 THEN '0'
ELSE ''
END +
CAST(month(@curdate) AS varchar(2)) +
' ' + DateName(m,@curdate),
 
Day(@curdate),
DateName(weekday,@curdate),
 
-- the quarter number
'Q' + CAST(floor((month(@curdate)+2)/3) AS char(1))
)
 
-- increase iteration count and current date
SET @i += 1
SET @curdate = DateAdd(day,1,@curdate)
 
-- quick check we haven't got a ridiculous loop
IF @i > 36600
BEGIN
SELECT 'More than 100 years!'
RETURN
END
END

-- try this out for 2013 dates
spCreateCalendarTable '20130101', '20131231'
 
-- see if it worked
SELECT * FROM tblCalendar

SQL Server 2000

-- first create the table of dates
CREATE TABLE tblCalendar(
[Date] datetime PRIMARY KEY,
[Year] int,
MonthNumber int,
[MonthName] varchar(10),
MonthNameSorted varchar(20),
DayNumber int,
[DayName] varchar(10),
[Quarter] char(2)
)
 
-- now add one date at a time
DECLARE @StartDate datetime 
set @StartDate= '2000-01-01'
DECLARE @EndDate datetime 
set @EndDate= '2020-12-31'
DECLARE @i int 
set @i= 0
DECLARE @curdate datetime 
set @curdate= @StartDate
 
WHILE @curdate <= @EndDate
BEGIN
 
-- add a record for this date (could use FORMAT
-- function if SQL Server 2000 or later)
INSERT INTO tblCalendar (
[Date],
[Year],
MonthNumber,
[MonthName],
MonthNameSorted,
DayNumber,
[DayName],
[Quarter]
) VALUES (
@curdate,
Year(@curdate),
Month(@curdate),
DateName(m,@curdate),
 
-- get month name as eg "01 January" or "11 November"
CASE
WHEN month(@curdate) < 10 THEN '0'
ELSE ''
END +
CAST(month(@curdate) AS varchar(2)) +
' ' + DateName(m,@curdate),
 
Day(@curdate),
DateName(weekday,@curdate),
 
-- the quarter number
'Q' + CAST(floor((month(@curdate)+2)/3) AS char(1))
)
 
-- increase iteration count and current date
SET @i = @i+1
SET @curdate = DateAdd(day,1,@curdate)
 
-- quick check we haven't got a ridiculous loop
IF @i > 36600
BEGIN
SELECT 'More than 100 years!'
RETURN
END
END

select * from tblCalendar