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

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

(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 日期
<wbr> FROM master..spt_values a --系统表<br><wbr> WHERE a.type = 'P'<wbr><br><wbr> AND number BETWEEN 0 AND (select DATEDIFF(dd,@sdate,@edate))</wbr></wbr></wbr></wbr>

结果如下:

日期
----------
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 行受影响)