日期轉(zhuǎn)為星期顯示的SQL

昵稱10504424 2013-03-15

展開全文

根據(jù)月份得出日歷,求一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

（所影響的行數(shù)為 6 行）
*/

----------------------------------------------------------------------
用函數(shù)解決。（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

分享到：

本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自：昵稱10504424 > 《SqlServer》

舉報/認(rèn)領(lǐng)