SQL Server Dates or Calendar Table for PowerPivot

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

SQL Server 2012

 1 CREATE PROC spCreateCalendarTable(
 2 @StartDate datetime = '20000101',
 3 @EndDate datetime = '20201231'
 4 ) AS
 5  
 6 -- create a table of dates for use in PowerPivot
 7  
 8 -- NOT FOR COMMERCIAL USE OR REDISTRIBUTION
 9 -- WITHOUT PRIOR WRITTEN PERMISSION FROM WISE OWL
10  
11 -- get rid of any old versions of table
12 BEGIN TRY
13 DROP TABLE tblCalendar
14 END TRY
15  
16 BEGIN CATCH
17 END CATCH
18  
19 -- first create the table of dates
20 CREATE TABLE tblCalendar(
21 [Date] datetime PRIMARY KEY,
22 [Year] int,
23 MonthNumber int,
24 [MonthName] varchar(10),
25 MonthNameSorted varchar(20),
26 DayNumber int,
27 [DayName] varchar(10),
28 [Quarter] char(2)
29 )
30  
31 -- now add one date at a time
32 DECLARE @i int = 0
33 DECLARE @curdate datetime = @StartDate
34  
35 WHILE @curdate <= @EndDate
36 BEGIN
37  
38 -- add a record for this date (could use FORMAT
39 -- function if SQL Server 2012 or later)
40 INSERT INTO tblCalendar (
41 [Date],
42 [Year],
43 MonthNumber,
44 [MonthName],
45 MonthNameSorted,
46 DayNumber,
47 [DayName],
48 [Quarter]
49 ) VALUES (
50 @curdate,
51 Year(@curdate),
52 Month(@curdate),
53 DateName(m,@curdate),
54  
55 -- get month name as eg "01 January" or "11 November"
56 CASE
57 WHEN month(@curdate) < 10 THEN '0'
58 ELSE ''
59 END +
60 CAST(month(@curdate) AS varchar(2)) +
61 ' ' + DateName(m,@curdate),
62  
63 Day(@curdate),
64 DateName(weekday,@curdate),
65  
66 -- the quarter number
67 'Q' + CAST(floor((month(@curdate)+2)/3) AS char(1))
68 )
69  
70 -- increase iteration count and current date
71 SET @i += 1
72 SET @curdate = DateAdd(day,1,@curdate)
73  
74 -- quick check we haven't got a ridiculous loop
75 IF @i > 36600
76 BEGIN
77 SELECT 'More than 100 years!'
78 RETURN
79 END
80 END
81 
82 -- try this out for 2013 dates
83 spCreateCalendarTable '20130101', '20131231'
84  
85 -- see if it worked
86 SELECT * FROM tblCalendar

SQL Server 2000

 1 -- first create the table of dates
 2 CREATE TABLE tblCalendar(
 3 [Date] datetime PRIMARY KEY,
 4 [Year] int,
 5 MonthNumber int,
 6 [MonthName] varchar(10),
 7 MonthNameSorted varchar(20),
 8 DayNumber int,
 9 [DayName] varchar(10),
10 [Quarter] char(2)
11 )
12  
13 -- now add one date at a time
14 DECLARE @StartDate datetime 
15 set @StartDate= '2000-01-01'
16 DECLARE @EndDate datetime 
17 set @EndDate= '2020-12-31'
18 DECLARE @i int 
19 set @i= 0
20 DECLARE @curdate datetime 
21 set @curdate= @StartDate
22  
23 WHILE @curdate <= @EndDate
24 BEGIN
25  
26 -- add a record for this date (could use FORMAT
27 -- function if SQL Server 2000 or later)
28 INSERT INTO tblCalendar (
29 [Date],
30 [Year],
31 MonthNumber,
32 [MonthName],
33 MonthNameSorted,
34 DayNumber,
35 [DayName],
36 [Quarter]
37 ) VALUES (
38 @curdate,
39 Year(@curdate),
40 Month(@curdate),
41 DateName(m,@curdate),
42  
43 -- get month name as eg "01 January" or "11 November"
44 CASE
45 WHEN month(@curdate) < 10 THEN '0'
46 ELSE ''
47 END +
48 CAST(month(@curdate) AS varchar(2)) +
49 ' ' + DateName(m,@curdate),
50  
51 Day(@curdate),
52 DateName(weekday,@curdate),
53  
54 -- the quarter number
55 'Q' + CAST(floor((month(@curdate)+2)/3) AS char(1))
56 )
57  
58 -- increase iteration count and current date
59 SET @i = @i+1
60 SET @curdate = DateAdd(day,1,@curdate)
61  
62 -- quick check we haven't got a ridiculous loop
63 IF @i > 36600
64 BEGIN
65 SELECT 'More than 100 years!'
66 RETURN
67 END
68 END
69 
70 select * from tblCalendar

 

posted @ 2013-03-06 10:32  ®Geovin Du Dream Park™  阅读(408)  评论(0)  编辑  收藏  举报