如何度过一个月的日子

debugcn 发表于 Dev

用户名

我用这个查询来获得一个月中的整天

    declare @date datetime
    set @date = '20140201';

with DaysInMonth as (
    select @date as Date
    union all
    select dateadd(dd,1,Date) from DaysInMonth where month(date) = month(@Date)
    )
select * from DaysInMonth where month(date) = month(@Date)

并整日待在清单中

我如何获得此结果，并将这几天放在相应的一周中，例如本月：

Sun1 - Mon1 - TUE1 - WED1 -  THU1 - FRI1 - SAT1 - Sun2 - Mon2 - TUE2 - WED2 -  THU2 - FRI2 - SAT2
 -      -      -      -        -     -      1      2       3     4      5       6      7     8

pd：对应于星期的数字1和2

肯定的

可以吗？？？

declare @date datetime
    set @date = '20140301';

with DaysInMonth as (
    select @date as Date,DATENAME(DD,@date) as [DAY],DATENAME(WEEKDAY,@date) as [DAYNAME]
    union all
    select dateadd(dd,1,Date),DATENAME(DD,Date+1) as [DAY],DATENAME(WEEKDAY,Date+1) as [DAYNAME] from DaysInMonth where month(date) = month(@Date)
    )
select [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]
--Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday 
from 

(
  SELECT [DAY],[DAYNAME] FROM   DaysInMonth where month(date) = month(@Date)
) D
PIVOT (MIN([DAYNAME]) FOR [DAY] IN ([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]) ) AS PVT

我对您的确切要求的另一个答案

declare @date datetime
declare @lastWeek int
    set @date = '20140301';
    set @lastWeek = DATENAME(WW,@date)-1
;with DaysInMonth as (
    select @date as Date,
           DATENAME(DD,@date) as [DAY],
             DATENAME(WEEKDAY,@date)+cast(cast(DATENAME(WW,@date) as int)-@lastWeek as varchar(2)) as [DAYNAME]
    union all
    select dateadd(dd,1,Date),DATENAME(DD,Date+1) as [DAY],
              DATENAME(WEEKDAY,Date+1)+cast(cast(DATENAME(WW,Date+1) as int)-@lastWeek as varchar(2)) as [DAYNAME]
    from DaysInMonth 
    where month(date) = month(@Date)
    )
select 
Sunday1,Monday1,Tuesday1,Wednesday1,Thursday1,Friday1,Saturday1,
Sunday2,Monday2,Tuesday2,Wednesday2,Thursday2,Friday2,Saturday2,
Sunday3,Monday3,Tuesday3,Wednesday3,Thursday3,Friday3,Saturday3,
Sunday4,Monday4,Tuesday4,Wednesday4,Thursday4,Friday4,Saturday4,
Sunday5,Monday5,Tuesday5,Wednesday5,Thursday5,Friday5,Saturday5,
Sunday6,Monday6,Tuesday6,Wednesday6,Thursday6,Friday6,Saturday6
from
(
  SELECT [DAY],[DAYNAME] FROM   DaysInMonth where month(date) = month(@Date)
) D
     PIVOT (max([DAY]) FOR [DAYNAME] IN 
    (Sunday1,Monday1,Tuesday1,Wednesday1,Thursday1,Friday1,Saturday1,
    Sunday2,Monday2,Tuesday2,Wednesday2,Thursday2,Friday2,Saturday2,
    Sunday3,Monday3,Tuesday3,Wednesday3,Thursday3,Friday3,Saturday3,
    Sunday4,Monday4,Tuesday4,Wednesday4,Thursday4,Friday4,Saturday4,
    Sunday5,Monday5,Tuesday5,Wednesday5,Thursday5,Friday5,Saturday5,
    Sunday6,Monday6,Tuesday6,Wednesday6,Thursday6,Friday6,Saturday6
     )
 ) AS PVT

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。