澳门二十一点游戏赌场 澳门二十一点游戏赌场
    您现在的位置:首页 >> 软件应用 >> SQL教程 >> 内容

    T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及年的周次

    时间:2012/7/23 20:02:36 点击:

      核心提示:--增加了日期所在月及年的周次!--星期日要算在'上一周'!(注意 WeekOfYear、WeekOfMonth 与 MyWeekOfYear、MyWeekOfMonth 的区别)--注意 daten...
    --增加了日期所在月及年的周次!
    --星期日要算在"上一周"!(注意 WeekOfYear、WeekOfMonth 与 MyWeekOfYear、MyWeekOfMonth 的区别)
    --注意 datename 的值会因 SQL Server 语言版本或日期格式有所差异!
    --本测试环境为: SQL Server 2000 简体中文版 + Windows 简体中文版

    declare @ datetime
    set @ = '1995-02-25 11:00:50' -- 1995-01-01 正好是个星期日

    select @ as 日期
          ,dateadd(year,datediff(year,0,@),0) as 所在年的第一天
          ,dateadd(year,1+datediff(year,0,@),0)-1 as 所在年的最后一天
          ,dateadd(quarter,datediff(quarter,0,@),0) as 所在季的第一天
          ,dateadd(quarter,1+datediff(quarter,0,@),0)-1 as 所在季的最后一天
          ,dateadd(month,datediff(month,0,@),0) as 所在月的第一天
          ,dateadd(month,1+datediff(month,0,@),0)-1 as 所在月的最后一天
          ,dateadd(week,datediff(week,0,@),0) as 所在周的第一天
          ,dateadd(week,1+datediff(week,0,@),0)-1 as 所在周的最后一天

    select dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))) as [Date]
    ,datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as [WeekDayName]
    ,datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as [WeekDay]
    ,datepart(week,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as WeekOfYear
    ,datediff
    (
    week
    ,case when datename(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0)))) = '星期日'
               then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0))))
          else dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0)))
    end    

    ,case when datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) = '星期日'
               then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
          else dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))
    end    
    )
    + 1 as MyWeekOfYear

    ,datediff(week,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) +1 as WeekOfMonth
    ,datediff
    (week
    ,case when datename(weekday,dateadd(day,1-day(dateadd(day,0,datediff(|<< << < 1 2 3 > >> >>|

    Tags:TS SQ QL L生 
    作者:佚名 来源:不详

    共有评论 0相关评论
    发表我的评论
    • 大名:
    • 内容:
  • 澳门二十一点游戏赌场(www.0769online.com) © 2018 版权所有 All Rights Reserved. 邮箱:103150@qq.com 业务QQ:103150 手机:13549753683

    Power By Laoy8 SQL 3.0SP1 粤ICP备10098668号
  •