--增加了日期所在月及年的周次!
--星期日要算在"上一周"!(注意 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]
,(@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) % 7 as [MyWeekDay]
,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 (@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0))))) % 7 = 1
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))) --date 所在年的第一天 即: 一月一号
end
,case when (@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) % 7 = 1
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 (@@datefirst + datepart(weekday,dateadd(month,datediff(month,0,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),0))) % 7 = 1
then dateadd(month,datediff(month,0,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),0) -1
else dateadd(month,datediff(month,0,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),0)
end
,case when (@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) % 7 = 1
then dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))-1
else dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))
end
) + 1 as MyWeekOfMonth
,datepart(dayofyear,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as DayOfYear
from
(
select 0 as i
union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9
union all select 10 union all select 11
) M
,
(
select 0 as i
union all select 1 union all select 2 union all select 3 union all select 4 union all select 5
union all select 6 union all select 7 union all select 8 union all select 9 union all select 10
union all select 11 union all select 12 union all select 13 union all select 14
union all select 15 union all select 16 union all select 17 union all select 18
union all select 19 union all select 20 union all select 21 union all select 22
union all select 23 union all select 24 union all select 25 union all select 26
union all select 27 union all select 28 union all select 29 union all select 30
) d
where datediff(month,dateadd(year,datediff(year,0,@),0),dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))) = m.i
order by [Date]
分享到:
相关推荐
vue或者js获取指定标准时间所在年的周次
最近在制作一个报表,里面需要计算某日是属于当年的多少周,并且要显示这周的起始日期和截止日期,本以为网上会有很多,可是找来找去,要么是要用WEEKNUM()这函数,需要加分析工具库,通用性不是很强,要么就不是...
-万年历
自己设定每周的第一天是哪一天! 实现要求:每年的第一天属于第一周!计算每年的总周次!
每年第一天为第一周的周次算法 QQ交流群60168829 欢迎C# ASP.NET 和SQL新手 老手 高手加入 谢谢
1、动态设置判断条件 2、动态设置每周的第一天
ABAP日期函数(求月末日,第几周,search help 只显示年月) 使用方法实例
九年级数学教学计划含教学进度(周次).pdf
2021部编版五年级下册道德与法治教学进度表教学安排表[附周次起止日期].pdf
2021部编版三年级下册道德与法治教学进度表教学安排表[附周次起止日期].pdf
NULL 博文链接:https://lijun0349-163-com.iteye.com/blog/616318
第一章 C语言 PTA 基本数据类型与表达式——作业-答案.html
先依次录入: 1.学期开始日(周一),学期周数 2.本学期调停情况 ...不在一个教室上课! 1-8周上课,或者3-18周上课,还是5-15周上课,或者是其他加仨儿时间上课! 单双周上课节数不同的课! 运动会 节假日调课停课!
2、每个月的日期列表横向显示在一排,而不是像网上的很多日期控件显示一个方块。 3、要求只有年月日可选,选择年或月后自动更新对应日期(这个每个日期控件都有的)。 4、默认显示当前年月,高亮当前日期,并显示...
extend方法、multiple日历的shortcut设置2021/02/01 V1.2.0(已发布)周次选择器iconfont从cdn改为本地修复部分bug2021/01/28 V1.1.0(已发布)更新功能点移除moment.js,改为dayjs处理初始化日期为空的状态增加不可选...
周历显示的文件,可以显示1年内的所有周,当前周用显著色提示,点击周可以执行相关操作,比如写工作周报什么的,显示相关内容什么的。
调用了两个模块、一个自编DLL setWindowRevealAPI.ec 调用系统API,设置窗口的透明效果,源码在同级目录下的setWindowRevealAPI.e 窗口阴影模块.ec 设置窗口的阴影,By:邓学彬,源码在同级目录下的窗口阴影模块.e ...
各栏分别表示周次、星期/节、日期。 复制后,选中Word表格中足够多的行数,粘贴即可自动填入各行中,非常便于制作各种星期时间相关的进度表。 v3.0更新: 修复一些错误; 适应常用浏览器;
4、数据标识维护里,数据项的通配符增加@GetDateWeekTime@(当前日期(周次) 时间数据块,格式YYMMDDWWhhmmss)。 5、在编辑标识窗口中增加通配符可拷贝界面。 6、完善表号选择。 7、修复广播超时bug...
Java 语言的Calendar(日历),Date(日期),和DateFormat(日期格式)组成了Java标准的一个基本但是非常重要的部分。日期是商业逻辑计算一个关键的部分。下面这篇文章就给大家介绍了如何利用Java中Calendar计算两个日期...