SQL做日历，挑战一下吧，100分
- 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 行受影响)
[解决办法]
- SQL code
declare @dt datetimeset @dt= '2010-12-15'select SUN = MAX(case when DATEPART(WEEKDAY,DATEADD(day,(a+6*b)*c,@dt )) = 1 then DAY(DATEADD(day,(a+6*b)*c,@dt)) end),MON = MAX(case when DATEPART(WEEKDAY,DATEADD(day,(a+6*b)*c,@dt )) = 2 then DAY(DATEADD(day,(a+6*b)*c,@dt)) end),TUE = MAX(case when DATEPART(WEEKDAY,DATEADD(day,(a+6*b)*c,@dt )) = 3 then DAY(DATEADD(day,(a+6*b)*c,@dt)) end),WED = MAX(case when DATEPART(WEEKDAY,DATEADD(day,(a+6*b)*c,@dt )) = 4 then DAY(DATEADD(day,(a+6*b)*c,@dt)) end),THU = MAX(case when DATEPART(WEEKDAY,DATEADD(day,(a+6*b)*c,@dt )) = 5 then DAY(DATEADD(day,(a+6*b)*c,@dt)) end),FRI = MAX(case when DATEPART(WEEKDAY,DATEADD(day,(a+6*b)*c,@dt )) = 6 then DAY(DATEADD(day,(a+6*b)*c,@dt)) end),SAT = MAX(case when DATEPART(WEEKDAY,DATEADD(day,(a+6*b)*c,@dt )) = 7 then DAY(DATEADD(day,(a+6*b)*c,@dt)) end)from ( select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 ) as a,( select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 ) as b,( select 1 as c union all select -1 ) as cwhere DATEADD(day,(a+6*b)*c,@dt ) >= CONVERT(varchar(7),@dt,120) + '-01'and DATEADD(day,(a+6*b)*c,@dt ) < dateadd(month,1,CONVERT(varchar(7),@dt,120) + '-01')group by DATEPART(week,DATEADD(day,(a+6*b)*c,@dt ))SUN MON TUE WED THU FRI SAT----------- ----------- ----------- ----------- ----------- ----------- -----------NULL NULL NULL 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 NULL警告: 聚合或其他 SET 操作消除了 Null 值。(5 行受影响)
[解决办法]
- SQL code
根据月份得出日历,求一sql 日 一 二 三 四 五 六 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 像这样的 ---------------------------------------declare @month as varchar(7)set @month = '2007-12'select 日,一,二,三,四,五,六 from(select week , max(case weekday when 1 then datename(day,dt) else '' end ) '日', max(case weekday when 2 then datename(day,dt) else '' end ) '一', max(case weekday when 3 then datename(day,dt) else '' end ) '二', max(case weekday when 4 then datename(day,dt) else '' end ) '三', max(case weekday when 5 then datename(day,dt) else '' end ) '四', max(case weekday when 6 then datename(day,dt) else '' end ) '五', max(case weekday when 7 then datename(day,dt) else '' end ) '六'from( select week = datepart(week , m.dt) , weekday = datepart(weekday , m.dt) , dt from ( select dt = @month + '-' + right('00'+cast(t.id as varchar),2) from ( select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12 union select 13 union select 14 union select 15 union select 16 union select 17 union select 18 union select 19 union select 20 union select 21 union select 22 union select 23 union select 24 union select 25 union select 26 union select 27 union select 28 union select 29 union select 30 union select 31 ) t where isdate(@month + '-' + right('00'+cast(t.id as varchar),2)) = 1 and @month + '-' + right('00'+cast(t.id as varchar),2) <= dateadd(month , 1 , @month + '-01') ) m) ngroup by week) o/*日 一 二 三 四 五 六 -- -- -- -- -- -- -- 12 3 4 5 6 7 89 10 11 12 13 14 1516 17 18 19 20 21 2223 24 25 26 27 28 2930 31 （所影响的行数为 6 行）*/----------------------------------用函数解决。（libin_ftsafe）create function f_calendar(@year int,@month int)returns @t table(日 varchar(4),一 varchar(4),二 varchar(4),三 varchar(4),四 varchar(4),五 varchar(4),六 varchar(4))asbegin declare @a table(id int identity(0,1),date datetime) insert into @a(date) select top 31 rtrim(@year)+'-'+rtrim(@month)+'-1' from sysobjects update @a set date=dateadd(dd,id,date) insert into @t select max(case datepart(dw,date) when 7 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 1 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 2 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 3 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 4 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 5 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 6 then rtrim(day(date)) else '' end) from @a where month(date)=@month group by (case datepart(dw,date) when 7 then datepart(week,date)+1 else datepart(week,date) end) returnendgoset datefirst 1select * from dbo.f_calendar(2007,12)/*日 一 二 三 四 五 六 ---- ---- ---- ---- ---- ---- ---- 12 3 4 5 6 7 89 10 11 12 13 14 1516 17 18 19 20 21 2223 24 25 26 27 28 2930 31 */godrop function f_calendargo
[解决办法]
- SQL code
--去年没事写的玩的--获取每个月日历CREATE PROC GetEveryDayOfMonth(@date datetime)ASBEGINSET NOCOUNT ONSET datefirst 1DECLARE @DateBegin DateTime,@DateEnd DateTimeDECLARE @Sun varchar(2),@Mon varchar(2),@Tue varchar(2),@Wed varchar(2), @Thu varchar(2),@Fir varchar(2),@Sta varchar(2) SET @DateBegin=CONVERT(VARCHAR(7),@date,120)+'-01' SET @DateEnd=DATEADD(DAY,-1,DATEADD(MONTH,1,@DateBegin)) DECLARE @t table([日] varchar(3),[一] varchar(3),[二] varchar(3),[三] varchar(3), [四] varchar(3),[五] varchar(3),[六] varchar(3)) WHILE @DateBegin<=@DateEnd BEGIN IF DATEPART(WEEKDAY,@DateBegin)=7 SET @Sun=RTRIM(DAY(@DateBegin)) IF DATEPART(WEEKDAY,@DateBegin)=1 SET @Mon=RTRIM(DAY(@DateBegin)) IF DATEPART(WEEKDAY,@DateBegin)=2 SET @Tue=RTRIM(DAY(@DateBegin)) IF DATEPART(WEEKDAY,@DateBegin)=3 SET @Wed=RTRIM(DAY(@DateBegin)) IF DATEPART(WEEKDAY,@DateBegin)=4 SET @Thu=RTRIM(DAY(@DateBegin)) IF DATEPART(WEEKDAY,@DateBegin)=5 SET @Fir=RTRIM(DAY(@DateBegin)) IF DATEPART(WEEKDAY,@DateBegin)=6 SET @Sta=RTRIM(DAY(@DateBegin)) IF @Sta<>'' --如果星期六不为空，那么说明一行已满开始新的一行。 BEGIN INSERT INTO @t([日],[一],[二],[三],[四],[五],[六])VALUES(ISNULL(@Sun,''),ISNULL(@Mon,''),ISNULL(@Tue,''),ISNULL(@Wed,''),ISNULL(@Thu,''),ISNULL(@Fir,''),ISNULL(@Sta,'')) SET @Sun='';SET @Mon='';SET @Tue='';SET @Wed='';SET @Thu='';SET @Fir='';SET @Sta='' END SET @DateBegin=Dateadd(DAY,1,@DateBegin) END IF @Sun<>'' OR @Mon<>'' OR @Tue<>'' OR @Wed<>'' OR @Thu<>'' OR @Fir<>'' OR @Sta<>'' INSERT INTO @t([日],[一],[二],[三],[四],[五],[六])VALUES(ISNULL(@Sun,''),ISNULL(@Mon,''),ISNULL(@Tue,''),ISNULL(@Wed,''),ISNULL(@Thu,''),ISNULL(@Fir,''),ISNULL(@Sta,''))SET datefirst 7SELECT * FROM @tSET NOCOUNT OFFEND--EXEC GetEveryDayOfMonth '2010-12-01'/*日 一 二 三 四 五 六---- ---- ---- ---- ---- ---- ---- 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 @dt datetimeset @dt = '2010-12-15';with t1 as( select dt = @dt-day(@dt)+number from master..spt_values a where type = 'P' and @dt-day(@dt)+number <= dateadd(m,1,@dt)-day(@dt) and number>0),t2 as( select wk = substring(datename(weekday,dt),3,1), ww = datepart(week,dt),dd = day(dt) from t1)select * from t2 pivot (max(dd) for wk in ([日],[一],[二],[三],[四],[五],[六])) b/*ww 日 一 二 三 四 五 六----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------49 NULL NULL NULL 1 2 3 450 5 6 7 8 9 10 1151 12 13 14 15 16 17 1852 19 20 21 22 23 24 2553 26 27 28 29 30 31 NULL(5 行受影响)*/
[解决办法]
- SQL code
CREATE PROCEDURE dbo.GetCalendar@Date datetime = NULLASSET NOCOUNT ONIF @Date IS NULL SET @Date = GETDATE()SELECT [MONTH] = CONVERT(char(7),@Date,120)DECLARE @MonthBDate datetime, @MonthEDate datetimeSET @MonthBDate = CONVERT(char(6),@Date,112)+'01'SET @MonthEDate = DATEADD(day,-1,DATEADD(month,1,@MonthBDate))SELECT SUN = ISNULL([0],''), MON = ISNULL([1],''), TUE = ISNULL([2],''), WED = ISNULL([3],''), THU = ISNULL([4],''), FRI = ISNULL([5],''), SAT = ISNULL([6],'')FROM ( SELECT DayNum = CAST(DAY(Date) AS varchar(2)) + CASE WHEN DAY(Date) = DAY(@Date) THEN '*' ELSE '' END, WeekNum = DAY(Date) - DATEPART(weekday,Date+@@DATEFIRST), WeekDayNum = DATEPART(weekday,Date+@@DATEFIRST) - 1 FROM ( SELECT Date = DATEADD(day,n-1,@MonthBDate) FROM dbo.Nums WHERE n <= DATEDIFF(day,@MonthBDate,@MonthEDate) + 1 ) cal) tmpPIVOT(MIN(DayNum) FOR WeekDayNum IN ([0], [1], [2], [3], [4], [5], [6])) pvtORDER BY WeekNumSET NOCOUNT OFFGO--无论@@DATEFIRST设为多少，结果都正常SET DATEFIRST 7SELECT @@DATEFIRSTEXEC dbo.GetCalendarEXEC dbo.GetCalendar '20090101'
[解决办法]
- SQL code
create function fn_calendar(@date datetime) returns @t table (日 varchar(2), 一 varchar(2), 二 varchar(2), 三 varchar(2), 四 varchar(2), 五 varchar(2), 六 varchar(2))asbegin declare @offset int, @max int set @date = convert(varchar(6), @date, 112) + '01' set @offset = (@@datefirst + datepart(weekday, @date) - 1) % 7 set @max = 33 - day(@date + 31) declare @month table(id tinyint identity, date datetime, week tinyint) insert @month select top 31 null, null from sysobjects update @month set date = @date + id - 1, week = (id + @offset - 1) % 7 insert @t select max(case week when 0 then ltrim(id) else '' end), max(case week when 1 then ltrim(id) else '' end), max(case week when 2 then ltrim(id) else '' end), max(case week when 3 then ltrim(id) else '' end), max(case week when 4 then ltrim(id) else '' end), max(case week when 5 then ltrim(id) else '' end), max(case week when 6 then ltrim(id) else '' end) from @month where id < @max group by (id + @offset - 1) / 7 returnendgoselect * from fn_calendar('2008-05-01')
[解决办法]
- SQL code
CREATE PROCEDURE dbo.GetCalendar2@Date datetime = NULLASSET NOCOUNT ONIF @Date IS NULL SET @Date = GETDATE()SELECT [MONTH] = CONVERT(char(7),@Date,120)DECLARE @MonthBDate datetime, @MonthEDate datetimeSET @MonthBDate = CONVERT(char(6),@Date,112)+'01'SET @MonthEDate = DATEADD(day,-1,DATEADD(month,1,@MonthBDate))DECLARE @sql nvarchar(4000)DECLARE @columns nvarchar(200);WITH WeekNames AS( SELECT k = 0, v = 'SUN' UNION ALL SELECT k = 1, v = 'MON' UNION ALL SELECT k = 2, v = 'TUE' UNION ALL SELECT k = 3, v = 'WED' UNION ALL SELECT k = 4, v = 'THU' UNION ALL SELECT k = 5, v = 'FRI' UNION ALL SELECT k = 6, v = 'SAT')SELECT @columns = STUFF(( SELECT TOP(7) ', ' + v + ' = ISNULL([' + CAST(k AS varchar(1)) + '],'''')' FROM ( SELECT g = 0, k, v FROM WeekNames UNION ALL SELECT g = 1, k, v FROM WeekNames ) tmp WHERE g * 7 + k >= ISNULL(NULLIF(@@DATEFIRST,7),0) ORDER BY g, k FOR XML PATH('') ),1,2,'')SET @sql = 'SELECT ' + @columns + 'FROM ( SELECT DayNum = CAST(DAY(Date) AS varchar(2)) + CASE WHEN DAY(Date) = DAY(@Date) THEN ''*'' ELSE '''' END, WeekNum = DATEPART(week,Date), WeekDayNum = DATEPART(weekday,Date+@@DATEFIRST) - 1 FROM ( SELECT Date = DATEADD(day,n-1,@MonthBDate) FROM dbo.Nums WHERE n <= DATEDIFF(day,@MonthBDate,@MonthEDate) + 1 ) cal) tmpPIVOT(MIN(DayNum) FOR WeekDayNum IN ([0], [1], [2], [3], [4], [5], [6])) pvtORDER BY WeekNum'EXEC sp_executesql @sql, N'@Date datetime, @MonthBDate datetime, @MonthEDate datetime', @Date = @Date, @MonthBDate = @MonthBDate, @MonthEDate = @MonthEDateSET NOCOUNT OFFGO--根据@@DATEFIRST设置显示结果SET DATEFIRST 7SELECT @@DATEFIRSTEXEC dbo.GetCalendar2EXEC dbo.GetCalendar2 '20090101'
[解决办法]
凑数娱乐:- SQL code
declare @dt datetimeset @dt='2010-12-15';with cte as(select dateadd(dd,number-day(@dt)+1,@dt) dtfrom master..spt_values where type='p' and month(@dt)=month(dateadd(dd,number-day(@dt)+1,@dt))and number<31)select [日],[一],[二],[三],[四],[五],[六] from(select datepart(wk,dt)wk,right(datename(dw,dt),1)dw,day(dt)d from cte)t pivot(max(d) for dw in([日],[一],[二],[三],[四],[五],[六]))b/*日 一 二 三 四 五 六----------- ----------- ----------- ----------- ----------- ----------- -----------NULL NULL NULL 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 NULL(5 行受影响)*/
[解决办法]
- SQL code
declare @dt datetimeset @dt='2010-12-15';with cte as(select dateadd(dd,number-day(@dt)+1,@dt) dtfrom master..spt_values where type='p' and month(@dt)=month(dateadd(dd,number-day(@dt)+1,@dt))and number<31)select isnull(日,'')日,isnull(一,'')一,isnull(二,'')二,isnull(三,'')三,isnull(四,'')四,isnull(五,'')五,isnull(六,'')六 from(select datepart(wk,dt)wk,right(datename(dw,dt),1)dw,convert(varchar(8),day(dt))d from cte)t pivot(max(d) for dw in(日,一,二,三,四,五,六))b/*日 一 二 三 四 五 六-------- -------- -------- -------- -------- -------- -------- 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 行受影响)*/
[解决办法]
我想到一种方法
- SQL code
set datefirst 1create table #T(week int,rq int)declare @tian intdeclare @i intselect @tian=day(dateadd(mm,1,getdate())-day(getdate()))select @i=0while @tian>0begininsert into #T(week,rq)select datepart(weekday,(getdate()-datepart(day,getdate()))+@tian),datepart(day,(getdate()-datepart(day,getdate()))+@tian)set @tian=@tian-1endselect case week when 1 then rq end as '1',case week when 2 then rq end as '2',case week when 3 then rq end as '3',case week when 4 then rq end as '4',case week when 5 then rq end as '5',case week when 6 then rq end as '6',case week when 7 then rq end as '7'from #torder by rq
[解决办法]
- SQL code
DECLARE @Year nvarchar(4)DECLARE @YearMonth nvarchar(7) --月份DECLARE @strTop nvarchar(200)DECLARE @ForI INT,@ForYear INT,@MaxDay INTDECLARE @RowX INT --行位置DECLARE @strWeekDayList nvarchar(20)DECLARE @strPrint nvarchar(300)SET @Year='2010' --显示的年份--设置日历上边的标题格式SET @strTop='日'+char(9)+'一'+char(9)+'二'+char(9)+'三'++char(9)+'四'++char(9)+'五'++char(9)+'六'+char(13)+ '————————————————————————————————'--设置星期列表SET @strWeekDayList='日一二三四五六'SET @ForYear=1WHILE @ForYear<=12 --1月份至12月份 BEGIN --取当月格式 SET @YearMonth=@Year+'-'+CAST(@ForYear AS nvarchar(2)) --取当月的最大日期 SET @MaxDay=DAY(DATEADD(Day,-1,DATEADD(Month,1,@YearMonth+'-01'))) --找出1号的开始位置 SET @RowX=CHARINDEX(RIGHT(DATENAME(WeekDay,@YearMonth+'-01'),1),@strWeekDayList)-1 SET @strPrint='' SET @ForI=1 WHILE @ForI<=@RowX --构造1号的位置，并绘画空白处 BEGIN SET @strPrint=@strPrint+CHAR(9) SET @ForI=@ForI+1 END SET @ForI=1 WHILE @ForI<=@MaxDay --构造2号到月底的位置，并绘画 BEGIN SET @strPrint=@strPrint+CAST(@ForI AS nvarchar(2))+CHAR(9) SET @RowX=@RowX+1 SET @ForI=@ForI+1 IF(@RowX%7=0) --满一个星期就换行 BEGIN SET @RowX=0 SET @strPrint=@strPrint+CHAR(13)--这里的数字CHAR(13)有什么特殊的含义？ END END SET @ForYear=@ForYear+1 --打印输出一个月的结果 PRINT '—————————————————————————————————' PRINT +CHAR(9)++CHAR(9)+' '+@YearMonth+CHAR(10) PRINT @strTop PRINT @strPrint+CHAR(10) END