闲着无聊,修改后的获取日历的存储过程
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 CREATE PROC [dbo].[GetCalendar2] ( @input DATE = NULL )
2 AS
3 BEGIN
4 IF @input IS NULL
5 SET @input = GETDATE() ;
6
7 WITH xx ( Day, WeekDay, Week, Date )
8 AS ( SELECT DAY(tt.Date) ,
9 DATEPART(dw, tt.Date) ,
10 DATEPART(ww, tt.Date) ,
11 tt.Date
12 FROM ( SELECT DATEADD(d, -DAY(@input) + 1,
13 @input) AS Date
14 ) AS tt
15 UNION ALL
16 SELECT xx.day + 1 ,
17 DATEPART(dw, DATEADD(d, 1, xx.Date)) ,
18 DATEPART(ww, DATEADD(d, 1, xx.Date)) ,
19 DATEADD(d, 1, xx.date)
20 FROM xx
21 WHERE DATEPART(m, DATEADD(d, 1, xx.Date)) = DATEPART(m,
22 xx.Date)
23 )
24 SELECT [1] AS Sunday ,
25 [2] AS Monday ,
26 [3] AS Tuesday ,
27 [4] AS Wednesday ,
28 [5] AS Thursday ,
29 [6] AS Friday ,
30 [7] AS Saturday
31 FROM ( SELECT xx.day ,
32 xx.WeekDay ,
33 xx.Week
34 FROM xx
35 ) AS p PIVOT( MAX(p.Day) FOR p.WeekDay IN ( [1], [2], [3],
36 [4], [5], [6],
37 [7] ) ) AS pvt
38 ORDER BY Week ASC
39 END
40 GO
41
2 AS
3 BEGIN
4 IF @input IS NULL
5 SET @input = GETDATE() ;
6
7 WITH xx ( Day, WeekDay, Week, Date )
8 AS ( SELECT DAY(tt.Date) ,
9 DATEPART(dw, tt.Date) ,
10 DATEPART(ww, tt.Date) ,
11 tt.Date
12 FROM ( SELECT DATEADD(d, -DAY(@input) + 1,
13 @input) AS Date
14 ) AS tt
15 UNION ALL
16 SELECT xx.day + 1 ,
17 DATEPART(dw, DATEADD(d, 1, xx.Date)) ,
18 DATEPART(ww, DATEADD(d, 1, xx.Date)) ,
19 DATEADD(d, 1, xx.date)
20 FROM xx
21 WHERE DATEPART(m, DATEADD(d, 1, xx.Date)) = DATEPART(m,
22 xx.Date)
23 )
24 SELECT [1] AS Sunday ,
25 [2] AS Monday ,
26 [3] AS Tuesday ,
27 [4] AS Wednesday ,
28 [5] AS Thursday ,
29 [6] AS Friday ,
30 [7] AS Saturday
31 FROM ( SELECT xx.day ,
32 xx.WeekDay ,
33 xx.Week
34 FROM xx
35 ) AS p PIVOT( MAX(p.Day) FOR p.WeekDay IN ( [1], [2], [3],
36 [4], [5], [6],
37 [7] ) ) AS pvt
38 ORDER BY Week ASC
39 END
40 GO
41
这里使用cte和pivot完成,和以前版本不一样,相比较以前的版本精简了代码,而且看起来更清晰一些