- SQL code
根据给定日期2010-12-15，得到这本年本月的日历。SUN MON TUE WED THU FRI SAT----------- ----------- ----------- ----------- ----------- ----------- ----------- 1 2 3 45 6 7 8 9 10 1112 13 14 15 16 17 1819 20 21 22 23 24 2526 27 28 29 30 31
------解决方案--------------------
- SQL code
DECLARE @DATE DATETIMESET @DATE=GETDATE()SELECT SUN=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))=1 THEN LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))) ELSE '' END),MON=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))=2 THEN LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))) ELSE '' END),TUE=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))=3 THEN LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))) ELSE '' END),WED=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))=4 THEN LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))) ELSE '' END),THU=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))=5 THEN LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))) ELSE '' END),FRI=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))=6 THEN LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))) ELSE '' END),SAT=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))=7 THEN LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))) ELSE '' END)FROM MASTER..SPT_VALUES WHERE TYPE='P' AND DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))<DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@DATE),@DATE))GROUP BY DATEPART(WEEK,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))/*SUN MON TUE WED THU FRI SAT------------ ------------ ------------ ------------ ------------ ------------ ------------ 1 2 3 45 6 7 8 9 10 1112 13 14 15 16 17 1819 20 21 22 23 24 2526 27 28 29 30 31 */
------解决方案--------------------
- SQL code
set datefirst 7declare @date datetimeset @date='2010-12-15' ;WITH CTE AS( select dd=dateadd(day,number,convert(varchar(8),@date,120)+'01'), DP=datepart(weekday,dateadd(day,number,convert(varchar(8),@date,120)+'01')) from master..spt_values where type='p' and number>=0 and dateadd(day,number,convert(varchar(8),@date,120)+'01')<convert(varchar(8),dateadd(month,1,@date),120)+'01')SELECT SUN,MON,TUE ,WED ,THU,FRI ,SATFROM ( SELECT N=datepart(week,dd), SUN=MAX(case when DP=1 then LTRIM(day(dd)) ELSE '' end), MON=MAX(case when DP=2 then LTRIM(day(dd)) ELSE '' end), TUE=MAX(case when DP=3 then LTRIM(day(dd)) ELSE '' end), WED=MAX(case when DP=4 then LTRIM(day(dd)) ELSE '' end), THU=MAX(case when DP=5 then LTRIM(day(dd)) ELSE '' end), FRI=MAX(case when DP=6 then LTRIM(day(dd)) ELSE '' end), SAT=MAX(case when DP=7 then LTRIM(day(dd)) ELSE '' end) FROM CTE GROUP BY datepart(week,dd))T SUN MON TUE WED THU FRI SAT------------ ------------ ------------ ------------ ------------ ------------ ------------ 1 2 3 45 6 7 8 9 10 1112 13 14 15 16 17 1819 20 21 22 23 24 2526 27 28 29 30 31 (5 行受影响)