|
--两个新年历
--增加了日期所在月及年的周次!
--星期日要算在"上一周"!(注意 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
when datename(weekday,min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))+2) = ''''星期二''''
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)))))) +2)=0
then min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))+2
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
when datename(weekday,min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))+2) = ''''星期三''''
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)))))) +2)=0
then min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))+2
when datename(weekday,min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))+3) = ''''星期三''''[1] [2] [3] [4] [5] [6] [7] 下一页
[Web开发]ASP.Net生成静态HTML页 [Web开发]asp.net c#版生成缩略图及给图片加水印之源代码
[Web开发]在ASP.NET中利用OWC生成柱状图和饼状图示例代码 [Web开发]asp.net(C#)上传大图片并生成缩略图源代码
[办公软件]在Word中使用VBA宏自动生成试卷的作文格子 [电脑技术]利用DOS命令生成光盘文件列表
[常用软件]在线生成便于记忆的强密码 [Web开发]ASP 生成 XBM 图可用作验证码
[Sql Server]求日期所属星座的 T-SQL UDF (用户自定义函数) [Sql Server]用于执行(计算) 字符串表达式 的 T-SQL 存储过程
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
