SQL Server时间算法总结
1: DECLARE @Date DATETIME
3: --前一天，给定日期的前一天 5: --后一天，给定日期的后一天 7: GO
9: 11: --这个计算的技巧是先计算当前日期到“1900-01-01”的时间间隔数，然后把它加到“1900-01-01”上来获得特殊的日期，这个技巧可以用---来计算很多不同的日期。 13: SET @Date=GETDATE()
15: --精简算法，根据SQL Server的时间表示方式可知，'1900-01-01' 可以用0代替
17: --上面两种算法到天 时分秒均为00:00:00.000 19: --思路：用给定日期减去月天与给定日期差的天数 21: GO
23: --月末，计算给定日期所在月的后一天 25: SET @Date=GETDATE()
27: SELECT DATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,'1900-01-01',@Date),'1900-01-01')) AS '所在月的一天'
29: --1900-01-01 用0代替 31: SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)-1 AS '所在月的一天'
33: SELECT DATEADD(MONTH,DATEDIFF(MONTH,'1989-12-31',@Date),'1989-12-31') AS '所在月的一天'
35: SELECT DATEADD(MONTH,DATEDIFF(MONTH,-1,@Date),-1) AS '所在月的一天'
37: SELECT DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)))
39: 41: 43: DECLARE @Date DATETIME
45: --当前月天减去一个月 47: --简化 49: --另一种当前月天算法 51: GO
53: --计算给定日期所在月的上月后一天 55: SET @Date=GETDATE()
57: SELECT DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0)) AS '上月后一天'
59: SELECT DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) '上月后一天'
61: --另一种算法，不能用当前月的后一天加一个月，因为当前月可能是30天。 63: --这也是月末算法采用下月天减1天计算的原因 65: --例如 SELECT DATEADD(MONTH,1,'2010-05-31') --结果是2010-06-30
67: SELECT DATEADD(MONTH,DATEDIFF(MONTH,-1,@Date)-1,-1)
69: SELECT DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) '上月后一天'
71: SELECT DATEADD(DAY,0-DATEPART(DAY,@Date),@Date) '上月后一天'
73: 75: DECLARE @Date DATETIME
77: --当前月天加一个月 79: --简化 81: --另一种当前月天算法 83: GO
85: --计算给定日期所在月的下月后一天 87: SET @Date=GETDATE()
89: SELECT DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0))) AS '下月后一天'
91: SELECT DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+2,0)) AS '下月后一天'
93: --另一种算法 95: --另一种当前月天算法 97: GO
99: --所在星期的天，计算给定日期所在星期的第1天(星期日为天) 101: SET @Date= GETDATE()
103: --思路：当前日期+星期日(每周的第1天)与当前日期的差的天数 105: SET DATEFIRST 7 -- 或者设置为美国英语SET LANGUAGE us_english; (星期日为天)
107: --星期日，与SQL Server语言版本或@@DATEFIRST无关 109: SELECT DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'
111: SELECT DATEADD(WEEK,DATEDIFF(WEEK,6,@Date),6) AS '所在星期的星期日'
113: 115: --所在星期的第二天，计算给定日期所在星期的第2天(星期日为天) 117: SET @Date= GETDATE()
119: --DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关
121: SELECT DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天，星期一'
123: --'1900-01-01' 是星期一，'1900-01-01' 再加上(当前日期与1900-01-01差的星期数)个星期
125: GO
127: --上个星期天，计算给定日期所在星期的上一个星期日(星期日为天) 129: SET @Date= GETDATE()
131: --DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关
133: SELECT DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期天，星期日'
135: SELECT DATEADD(DAY,-7,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期天，星期日'
137: SELECT DATEADD(DAY,-6-DATEPART(WEEKDAY,@Date),@Date) AS '上个星期天，星期日'
139: SELECT DATEADD(WEEK,-1+DATEDIFF(WEEK,-1,@Date),-1) AS '上个星期日'
141: SELECT DATEADD(WEEK,DATEDIFF(WEEK,6,@Date),-1) AS '上个星期日'
143: 145: --下个星期天，计算给定日期所在星期的下一个星期日(星期日为天) 147: SET @Date= GETDATE()
149: --DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关
151: SELECT DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期天，星期日'
153: SELECT DATEADD(DAY,7,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期天，星期日'
155: SELECT DATEADD(DAY,8-DATEPART(WEEKDAY,@Date),@Date) AS '下个星期天，星期日'
157: SELECT DATEADD(WEEK,1+DATEDIFF(WEEK,-1,@Date),-1) AS '下个星期日'
159: SELECT DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),6) AS '下个星期日'
161: 163: DECLARE @Date DATETIME
165: --DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关
167: SELECT DATEPART(WEEKDAY,@Date) --返回值 1-星期日，2-星期一，3-星期二......7-星期六
169: --下面算法与SQL Server语言版本或@@DATEFIRST无关 171: GO
173: 175: DECLARE @Date DATETIME
177: --年初，计算给定日期所在年的天 179: --年末，计算给定日期所在年的后一天 181: --上一年年初，计算给定日期所在年的上一年的天 183: --上一年年末，计算给定日期所在年的上一年的后一天 185: --下一年年初，计算给定日期所在年的下一年的天 187: --下一年年末，计算给定日期所在年的下一年的后一天 189: GO
191: --季度计算 193: SET @Date=GETDATE()
195: SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '当前季度的天'
197: SELECT DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '当前季度的后一天'
199: SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '当前季度的上个季度初'
201: SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '当前季度的上个季度末'
203: SELECT DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '当前季度的下个季度初'
205: SELECT DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '当前季度的下个季度末'
207: 209: DECLARE @Date DATETIME;
211: --本月度天与下月度天所差的天数 213: --借助变量简化 215: SELECT DATEDIFF(DAY,@Date,DATEADD(MONTH,1,@Date))
217: SELECT DAY(DATEADD(MONTH,DATEDIFF(MONTH,-1,@Date),-1))
219: 221: DECLARE @Date DATETIME;
223: --本季度天与下季度天所差的天数 225: --借助变量简化 227: SELECT DATEDIFF(DAY,@Date,DATEADD(QUARTER,1,@Date))
229: 231: DECLARE @Date DATETIME;
233: --本年度天与下年度天所差的天数 235: --借助变量简化 237: SELECT DATEDIFF(DAY,@Date,DATEADD(YEAR,1,@Date))
239: 241: --根据全年总天数判断 243: SET @Date = GETDATE()
245: WHEN 365 THEN '平年' ELSE '闰年' END
247: --给日期的上一年后一天加2个月，即为当年2月后一天 249: GO
251: --计算给定日期是当年的第几天 253: SET @Date = GETDATE()
255: SELECT DATENAME(DAYOFYEAR,@Date) [DayOfYear];
257: SELECT DATEDIFF(DAY,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),-1),@Date)[DayOfYear]
259: 261: DECLARE @Date DATETIME;
263: SELECT DATEPART(WEEK,@Date) [WeekOfYear]; --返回int型
265: GO
267: --计算给定日期是当年的第几月 269: SET @Date = GETDATE()
271: SELECT DATENAME(MONTH,@Date) [MonthOfYear]; --返回varchar型
273: GO
275: --计算给定日期是当年的第几季度 277: SET @Date = GETDATE()
279: SELECT DATENAME(QUARTER,@Date) [QuarterOfYear]; --返回varchar型
281: 283: DECLARE @Date DATETIME;
285: --思路，给定日期是当年的第几周-给定日期所在月天是当年的第几周 287: SELECT DATEPART(WEEK,@Date)-DATEPART(WEEK,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))+1 [WeekOfMonth]
289: 291: DECLARE @Date DATETIME;
293: --思路，1900-01-01(星期一)加上(给定日志所在月的月6号与1900-01-01差的周数)个周 295: --为什么不选5号？如果5号是星期六，那么周一就跑到上月了。小于5号与这个道理一样。 297: SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(DAY,6-DATEPART(DAY,@Date),@Date)),7) '所在月的第二个星期一'
299: SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(DAY,6-DATEPART(DAY,@Date),@Date)),8) '所在月的第二个星期二'
本文来源https://www.cnblogs.com/psunny/archive/2010/09/03/1817177.html
