• 通過SQL取出所有周六/周日的日期到Table


     
    create table SatSun([id] int identity(1,1),[date] datetime,[weekday] char(6))
    go

    declare @datetime datetime,@weekday char(6)
    set @datetime='2009-1-1'
    while @datetime<='2009-12-31'
    begin
    select @weekday=datename(weekday,@datetime)
    if @weekday= '星期六' insert SatSun([date],[weekday]) values(@datetime,@weekday)
    if @weekday='星期日' insert SatSun([date],[weekday]) values(@datetime,'星期天')
    select @datetime=dateadd(day,1,@datetime)
    end
    go

    select * from satsun

    drop table satsun

    /*
    id          date                                                   weekday
    ----------- ------------------------------------------------------ -------
    1           2009-01-06 00:00:00.000                                星期六
    2           2009-01-07 00:00:00.000                                星期天
    3           2009-01-13 00:00:00.000                                星期六
    4           2009-01-14 00:00:00.000                                星期天
    5           2009-01-20 00:00:00.000                                星期六
    6           2009-01-21 00:00:00.000                                星期天
    7           2009-01-27 00:00:00.000                                星期六
    8           2009-01-28 00:00:00.000                                星期天
    9           2009-02-03 00:00:00.000                                星期六
    ……
    100         2009-12-16 00:00:00.000                                星期天
    101         2009-12-22 00:00:00.000                                星期六
    102         2009-12-23 00:00:00.000                                星期天
    103         2009-12-29 00:00:00.000                                星期六
    104         2009-12-30 00:00:00.000                                星期天

    */(所影响的行数为 104 行)




    ****************************************************************************

    declare @t table(dd datetime)
    declare @d1 datetime
    select @d1='2009-1-1'
    while(year(@d1) <2008)
    begin
    insert into @t select @d1
    set @d1=dateadd(dd,1,@d1)
    end

    select * from @t where datepart(weekday,dd)=6 or datepart(weekday,dd)=7


    ****************************************************************************
    declare @i int
    declare @d datetime
    set @i=1
    set @d='2009-1-1'
    Create table #a (Dat datetime)
    while year(@d)=2009
    begin
    insert into #a values (@d )
    set @i=@i+1
    set @d=dateadd(day,1,@d)
    end
    select *,datepart(dw,Dat) as a from #a where datepart(dw,Dat) =1 or datepart(dw,Dat) =7

    ****************************************************************************
    /*
    功能: 计算在某一段时间内某周几(如星期一)的所有日期
    设计:XXX
    时间:2009-10
    */
    DECLARE @Date datetime
    DECLARE @StartDate datetime
    DECLARE @EndDate datetime
    DECLARE @WeekDay int
    DECLARE @i int

    SET DATEFIRST 7 --设置每周的第一天
    SET @StartDate='2006-01-01' --统计的开始日期
    SET @EndDate='2006-12-31' --统计的结束日期
    SET @WeekDay=1 --根据实际的@@DATEFIRST而定,一般默认是7,如 @StartDate='2006-01-01'时候, @WeekDay=3表示星期二
    SET @i=DATEPART(weekday,@StartDate)
    PRINT '每周的第1天设置@@DATEFIRST: '+CAST(@@DATEFIRST AS nvarchar(1))
    PRINT '开始日期对应一周的第几天: '+CAST(@i AS nvarchar(1))
    IF(@i<=@WeekDay AND @i<7)
    SET @i=@WeekDay-@i
    ELSE IF(@i<=@WeekDay AND @i=7)
    SET @i=@i-@WeekDay
    ELSE
    SET @i=@@DATEFIRST-@i+@WeekDay

    SET @Date=DATEADD(day,@i,@StartDate)
    WHILE @Date<=@EndDate
    BEGIN
    IF(@StartDate<=@Date) PRINT CONVERT(nvarchar(10),@Date,121)
    SET @Date=DATEADD(Week,1,@Date)
    END
    GO
  • 相关阅读:
    继续学习:C语言关键字
    继续学习C:运算符
    C语言小程序(八)、统计字母个数
    C语言小程序(七)、石头剪刀布
    飞思卡尔总结
    [原]NYOJ-子串和44
    C++函数重载详解
    趣味Shell
    C语言小程序(六)、数组操作
    C语言小程序(五)、数组查询
  • 原文地址:https://www.cnblogs.com/godwar/p/1742857.html
Copyright © 2020-2023  润新知