--两个新年历
--增加了日期所在月及年的周次!
--星期日要算在"上一周"!(注意 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 month(min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) as 月份
,datepart(week,min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) as weekofyear
,datediff(week
,dateadd(month,datediff(month,0,min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))),0)
,min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
) +1 as weekofmonth
,case when datename(weekday,min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) = '星期日'
then min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
else null end as 星期日
,case when datename(weekday,min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) = '星期一'
then min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
when datename(weekday,min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))+1) = '星期一'
and datediff(month,min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) +1)=0
then min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) +1
else null end as 星期一
,case when datename(weekday,min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) = '星期二'
then min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
when datename(weekday,min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))+1) = '星期二'
and datediff(month,min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) +1)=0
then min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))+1
