SQL做日历

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 行受影响)
    */