日期转为星期显示的SQL

根据月份得出日历,求一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

源：http://topic.csdn.net/u/20090321/12/81433b7d-d02a-4a88-9e59-4463d1d56da9.html#replyachor

分享到：