DECLARE @DATE DATETIME SET @DATE=GETDATE() SELECT SUN=MAX(CASE WHEN DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))=@DATE THEN '*' ELSE '' END+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+CASE WHEN DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))=@DATE THEN '*' ELSE '' END) ,MON=MAX(CASE WHEN DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))=@DATE THEN '*' ELSE '' END+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+CASE WHEN DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))=@DATE THEN '*' ELSE '' END) ,TUE=MAX(CASE WHEN DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))=@DATE THEN '*' ELSE '' END+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+CASE WHEN DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))=@DATE THEN '*' ELSE '' END) ,WED=MAX(CASE WHEN DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))=@DATE THEN '*' ELSE '' END+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+CASE WHEN DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))=@DATE THEN '*' ELSE '' END) ,THU=MAX(CASE WHEN DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))=@DATE THEN '*' ELSE '' END+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+CASE WHEN DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))=@DATE THEN '*' ELSE '' END) ,FRI=MAX(CASE WHEN DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))=@DATE THEN '*' ELSE '' END+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+CASE WHEN DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))=@DATE THEN '*' ELSE '' END) ,SAT=MAX(CASE WHEN DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))=@DATE THEN '*' ELSE '' END+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+CASE WHEN DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))=@DATE THEN '*' 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 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 */ 根据月份得出日历,求一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 ) n group by week ) o /* 日 一 二 三 四 五 六 -- -- -- -- -- -- -- 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 行） */ ---------------------------------------------------------------------- 用函数解决。（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)) as begin 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) return end go set datefirst 1 select * from dbo.f_calendar(2007,12) /* 日 一 二 三 四 五 六 ---- ---- ---- ---- ---- ---- ---- 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 */ go drop function f_calendar go --去年没事写的玩的 --获取每个月日历 CREATE PROC GetEveryDayOfMonth(@date datetime) AS BEGIN SET NOCOUNT ON SET datefirst 1 DECLARE @DateBegin DateTime,@DateEnd DateTime DECLARE @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 7 SELECT * FROM @t SET NOCOUNT OFF END --EXEC GetEveryDayOfMonth '2010-12-01' /* 日 一 二 三 四 五 六 ---- ---- ---- ---- ---- ---- ---- 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 @dt datetime set @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 4 50 5 6 7 8 9 10 11 51 12 13 14 15 16 17 18 52 19 20 21 22 23 24 25 53 26 27 28 29 30 31 NULL (5 行受影响) */ declare @dt datetime set @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 4 50 5 6 7 8 9 10 11 51 12 13 14 15 16 17 18 52 19 20 21 22 23 24 25 53 26 27 28 29 30 31 NULL (5 行受影响) */ create function fn_calendar(@date datetime) returns @t table (日 varchar(2), 一 varchar(2), 二 varchar(2), 三 varchar(2), 四 varchar(2), 五 varchar(2), 六 varchar(2)) as begin 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 return end go select * from fn_calendar('2008-05-01') 凑数娱乐: SQL code declare @dt datetime set @dt='2010-12-15' ;with cte as( select dateadd(dd,number-day(@dt)+1,@dt) dt from 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 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 NULL (5 行受影响) */