sqlserver 日历

DECLARE @date CHAR(10)
SET @date = '2010-12-16'
SET @date = convert(varchar(10),getdate(),120)

SELECT SUN = MAX(CASE WHEN a.xq = 1 THEN CONVERT(CHAR(2),a.number) ELSE '' END),
       MON = MAX(CASE WHEN a.xq = 2 THEN CONVERT(CHAR(2),a.number) ELSE '' END),
       TUE = MAX(CASE WHEN a.xq = 3 THEN CONVERT(CHAR(2),a.number) ELSE '' END),
       WED = MAX(CASE WHEN a.xq = 4 THEN CONVERT(CHAR(2),a.number) ELSE '' END),
       THU = MAX(CASE WHEN a.xq = 5 THEN CONVERT(CHAR(2),a.number) ELSE '' END),
       FRI = MAX(CASE WHEN a.xq = 6 THEN CONVERT(CHAR(2),a.number) ELSE '' END),
       SAT = MAX(CASE WHEN a.xq = 7 THEN CONVERT(CHAR(2),a.number) ELSE '' END) 
FROM (
      SELECT a.number  --本月号数
             ,xq = DATEPART(weekday,LEFT(@date,8)+ CONVERT(CHAR(2),a.number)) --星期几
             ,zs = DATEDIFF(week,CONVERT(char(7),LEFT(@date,8)+ CONVERT(CHAR(2),a.number),121)+ '-01 ',LEFT(@date,8)+ CONVERT(CHAR(2),a.number))+1 --本月周数
      FROM master..spt_values a
      WHERE a.type = 'P'
            AND number BETWEEN 1 AND (select DAY(DATEADD(dd,-DATEPART(dd,@date) ,DATEADD(mm,1,@date))))  --本月号数范围
      ) a
GROUP BY a.zs

SUN  MON  TUE  WED  THU  FRI  SAT
---- ---- ---- ---- ---- ---- ----
                         1    2
3    4    5    6    7    8    9
10   11   12   13   14   15   16
17   18   19   20   21   22   23
24   25   26   27   28   29   30
31                            

(6 行受影响)

凭空生成日期数据

DECLARE @sdate CHAR(10)
DECLARE @edate CHAR(10)
SET @sdate = '2010-12-16'--开始日期
SET @edate = '2011-2-16' --结束日期

SELECT CONVERT(varchar(10),DATEADD(dd,a.number,@sdate),120) as 日期
  FROM master..spt_values a --系统表
  WHERE a.type = 'P' 
  AND number BETWEEN 0 AND (select DATEDIFF(dd,@sdate,@edate))

结果如下:

日期
----------
2010-12-16
2010-12-17
2010-12-18
2010-12-19
2010-12-20
2010-12-21
2010-12-22
2010-12-23
2010-12-24
2010-12-25
2010-12-26
2010-12-27
2010-12-28
2010-12-29
2010-12-30
2010-12-31
2011-01-01
2011-01-02
2011-01-03
2011-01-04
2011-01-05
2011-01-06
2011-01-07
2011-01-08
2011-01-09
2011-01-10
2011-01-11
2011-01-12
2011-01-13
2011-01-14
2011-01-15
2011-01-16
2011-01-17
2011-01-18
2011-01-19
2011-01-20
2011-01-21
2011-01-22
2011-01-23
2011-01-24
2011-01-25
2011-01-26
2011-01-27
2011-01-28
2011-01-29
2011-01-30
2011-01-31
2011-02-01
2011-02-02
2011-02-03
2011-02-04
2011-02-05
2011-02-06
2011-02-07
2011-02-08
2011-02-09
2011-02-10
2011-02-11
2011-02-12
2011-02-13
2011-02-14
2011-02-15
2011-02-16

(63 行受影响)