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

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

--增加了日期所在月及年的周次!
--星期日要算在"上一周"!(注意 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)))