SQL Server时间算法大全 |
|
本文标签：SQL Server时间 下文对SQL Server时间时期方面的计算问题进行了整合集中，如果您对SQL Server时间算法方面感兴趣的话，不妨一看，对您学习SQL Server时间算法会有所帮助 。 1: DECLARE @Date DATETIME 2: SET @Date=GETDATE() 3: --前一天，给定日期的前一天
4: SELECT DATEADD(DAY,-1,@Date) AS 前一天 5: --后一天，给定日期的后一天
6: SELECT DATEADD(DAY,1,@Date) AS 后一天 7: GO 8:
9:
10: --月初，计算给定日期所在月的第一天
11: --这个计算的技巧是先计算当前日期到“1900-01-01”的时间间隔数，然后把它加到“1900-01-01”上来获得特殊的日期，这个技巧可以用---来计算很多不同的日期 。
12: DECLARE @Date DATETIME 13: SET @Date=GETDATE() 14: SELECT DATEADD(MONTH,DATEDIFF(MONTH,1900-01-01,@Date),1900-01-01) AS 所在月的第一天 15: --精简算法，根据SQL Server的时间表示方式可知，1900-01-01 可以用0代替 16: SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS 所在月的第一天 17: --上面两种算法精确到天 时分秒均为00:00:00.000
18: --下面算法课以保留时分秒
19: --思路：用给定日期减去月第一天与给定日期差的天数
20: SELECT DATEADD(DAY,1-DATEPART(DAY,@Date),@Date) 21: GO 22:
23: --月末，计算给定日期所在月的最后一天
24: DECLARE @Date DATETIME 25: SET @Date=GETDATE() 26: --思路：当前月的下一月1号在减1天
27: SELECT DATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,1900-01-01,@Date),1900-01-01)) AS 所在月的最一天 28: SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,1900-01-01,@Date),1900-01-01)-1 AS 所在月的最一天 29: --1900-01-01 用0代替
30: SELECT DATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS 所在月的最一天 31: SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)-1 AS 所在月的最一天 32: --思路：与月初计算思路相同
33: SELECT DATEADD(MONTH,DATEDIFF(MONTH,1989-12-31,@Date),1989-12-31) AS 所在月的最一天 34: --精简算法，1989-12-31 用-1代替 35: SELECT DATEADD(MONTH,DATEDIFF(MONTH,-1,@Date),-1) AS 所在月的最一天 36: --保留时分秒的算法
37: SELECT DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) 38: GO 39:
40: --其他月计算
41:
42: --计算给定日期所在月的上月第一天
43: DECLARE @Date DATETIME 44: SET @Date=GETDATE() 45: --当前月第一天减去一个月
46: SELECT DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0)) AS 上月第一天 47: --简化
48: SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS 上月第一天 49: --另一种当前月第一天算法
50: SELECT DATEADD(MONTH,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) 上月第一天 51: GO 52:
53: --计算给定日期所在月的上月最后一天
54: DECLARE @Date DATETIME 55: SET @Date=GETDATE() 56: --当前月第一天减去一天
57: SELECT DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0)) AS 上月最后一天 58: --另一种当前月第一天算法
59: SELECT DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) 上月最后一天 60: SELECT DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)-1 上月最后一天 61: --另一种算法，不能用当前月的最后一天加一个月，因为当前月可能是30天 。
62: --例如 SELECT DATEADD(MONTH,1,2010-06-30) --结果是2010-07-30而不是2010-07-31， 63: --这也是月末算法采用下月第一天减1天计算的原因
64: --但是如果计算月是31天择无此问题
65: --例如 SELECT DATEADD(MONTH,1,2010-05-31) --结果是2010-06-30 66: --因此下面算法是正确的，-1 表示1899-12-31 00:00:00.000-- SELECT CONVERT(DATETIME,-1) 67: SELECT DATEADD(MONTH,DATEDIFF(MONTH,-1,@Date)-1,-1) 68: --另一种当前月算法
69: SELECT DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) 上月最后一天 70: --简化
71: SELECT DATEADD(DAY,0-DATEPART(DAY,@Date),@Date) 上月最后一天 72: GO 73:
74: --计算给定日期所在月的下月第一天
75: DECLARE @Date DATETIME 76: SET @Date=GETDATE() 77: --当前月第一天加一个月
78: SELECT DATEADD(MONTH,1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0)) AS 下月第一天 79: --简化
80: SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS 下月第一天 81: --另一种当前月第一天算法
82: SELECT DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) 下月第一天 83: GO 84:
85: --计算给定日期所在月的下月最后一天
86: DECLARE @Date DATETIME 87: SET @Date=GETDATE() 88: --当前月第一天加2个月再减去1天
89: SELECT DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0))) AS 下月最后一天 90: --简化
91: SELECT DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+2,0)) AS 下月最后一天 92: SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+2,0)-1 AS 下月最后一天 93: --另一种算法
94: SELECT DATEADD(MONTH,DATEDIFF(MONTH,-1,@Date)+1,-1) 下月最后一天 95: --另一种当前月第一天算法
96: SELECT DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) 下月最后一天 97: GO 98:
99: --所在星期的第一天，计算给定日期所在星期的第1天(星期日为第一天)
100: DECLARE @Date DATETIME 101: SET @Date= GETDATE() 102: --与SQL Server语言版本相关的算法
103: --思路：当前日期+星期日(每周的第1天)与当前日期的差的天数
104: --DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关 105: SET DATEFIRST 7 -- 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天) 106: SELECT DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS 所在星期的第一天，星期日 107: --星期日，与SQL Server语言版本或@@DATEFIRST无关
108: --1989-12-31 是星期日，1989-12-31 再加上(当前日期与1989-12-31差的星期数)个星期 109: SELECT DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS 所在星期的星期日 110: --或者
111: SELECT DATEADD(WEEK,DATEDIFF(WEEK,6,@Date),6) AS 所在星期的星期日 112: GO 113:
114:
115: --所在星期的第二天，计算给定日期所在星期的第2天(星期日为第一天)
116: DECLARE @Date DATETIME 117: SET @Date= GETDATE() 118: --思路：当前日期+星期一(每周的第2天)与当前日期的差的天数
119: --DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关 120: SET DATEFIRST 7 -- 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天) 121: SELECT DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS 所在星期的第二天，星期一 122: --星期一，与SQL Server语言版本或@@DATEFIRST无关
123: --1900-01-01 是星期一，1900-01-01 再加上(当前日期与1900-01-01差的星期数)个星期 124: SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,@Date),0) AS 所在星期的星期一 125: GO 126:
127: --上个星期第一天，计算给定日期所在星期的上一个星期日(星期日为第一天)
128: DECLARE @Date DATETIME 129: SET @Date= GETDATE() 130: --思路：当前日志所在星期的星期日再减1周
131: --DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关 132: SET DATEFIRST 7 -- 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天) 133: SELECT DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS 上个星期第一天，星期日 134: --一周等于7天
135: SELECT DATEADD(DAY,-7,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS 上个星期第一天，星期日 136: --简化
137: SELECT DATEADD(DAY,-6-DATEPART(WEEKDAY,@Date),@Date) AS 上个星期第一天，星期日 138: --上个星期日，与SQL Server语言版本或@@DATEFIRST无关
139: SELECT DATEADD(WEEK,-1+DATEDIFF(WEEK,-1,@Date),-1) AS 上个星期日 140: --或者
141: SELECT DATEADD(WEEK,DATEDIFF(WEEK,6,@Date),-1) AS 上个星期日 142: GO 143:
144:
145: --下个星期第一天，计算给定日期所在星期的下一个星期日(星期日为第一天)
146: DECLARE @Date DATETIME 147: SET @Date= GETDATE() 148: --思路：当前日志所在星期的星期日再加1周
149: --DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关 150: SET DATEFIRST 7 -- 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天) 151: SELECT DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS 下个星期第一天，星期日 152: --一周等于7天
153: SELECT DATEADD(DAY,7,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS 下个星期第一天，星期日 154: --简化
155: SELECT DATEADD(DAY,8-DATEPART(WEEKDAY,@Date),@Date) AS 下个星期第一天，星期日 156: --下个星期日，与SQL Server语言版本或@@DATEFIRST无关
157: SELECT DATEADD(WEEK,1+DATEDIFF(WEEK,-1,@Date),-1) AS 下个星期日 158: --或者
159: SELECT DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),6) AS 下个星期日 160: GO 161:
162: --判断给定日期是星期几
163: DECLARE @Date DATETIME 164: SET @Date= GETDATE() 165: --DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关 166: SET DATEFIRST 7 -- 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天) 167: SELECT DATEPART(WEEKDAY,@Date) --返回值 1-星期日，2-星期一，3-星期二......7-星期六 168: --上面算法与SQL 语言版本或 @@DATEFIRST 相关 169: --下面算法与SQL Server语言版本或@@DATEFIRST无关
170: SELECT DATENAME(WEEKDAY,@Date) 星期 171: GO 172:
173:
174: --年度计算
175: DECLARE @Date DATETIME 176: SET @Date=GETDATE() 177: --年初，计算给定日期所在年的第一天
178: SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS 所在年的第一天 179: --年末，计算给定日期所在年的最后一天
180: SELECT DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS 所在年的最后一天 181: --上一年年初，计算给定日期所在年的上一年的第一天
182: SELECT DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS 所在年的上一年的第一天 183: --上一年年末，计算给定日期所在年的上一年的最后一天
184: SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),-1) AS 所在年的上一年的最后一天 185: --下一年年初，计算给定日期所在年的下一年的第一天
186: SELECT DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS 所在年的下一年的第一天 187: --下一年年末，计算给定日期所在年的下一年的最后一天
188: SELECT DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS 所在年的下一年的最后一天 189: GO 190:
191: --季度计算
192: DECLARE @Date DATETIME 193: SET @Date=GETDATE() 194: --季度初，计算给定日期所在季度的第一天
195: SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS 当前季度的第一天 196: --季度末，计算给定日期所在季度的最后一天
197: SELECT DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS 当前季度的最后一天 198: --上个季度初
199: SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS 当前季度的上个季度初 200: --上个季度末
201: SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS 当前季度的上个季度末 202: --下个季度初
203: SELECT DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS 当前季度的下个季度初 204: --下个季度末
205: SELECT DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS 当前季度的下个季度末 206: GO 207:
208: --计算给定日期所在月的天数
209: DECLARE @Date DATETIME; 210: SET @Date = GETDATE() 211: --本月度第一天与下月度第一天所差的天数
212: SELECT DATEDIFF(DAY,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0),DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) 213: --借助变量简化
214: SELECT @Date = DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) --本月度第一天 215: SELECT DATEDIFF(DAY,@Date,DATEADD(MONTH,1,@Date)) 216: --另一种思路：给定月最后一天的日期，记为本月天数
217: SELECT DAY(DATEADD(MONTH,DATEDIFF(MONTH,-1,@Date),-1)) 218: GO 219:
220: --计算给定日期所在季度的天数
221: DECLARE @Date DATETIME; 222: SET @Date = GETDATE() 223: --本季度第一天与下季度第一天所差的天数
224: SELECT DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0),DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0)) 225: --借助变量简化
226: SELECT @Date = DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) --本季度第一天 227: SELECT DATEDIFF(DAY,@Date,DATEADD(QUARTER,1,@Date)) 228: GO 229:
230: --计算给定日期所在年度的天数
231: DECLARE @Date DATETIME; 232: SET @Date = GETDATE() 233: --本年度第一天与下年度第一天所差的天数
234: SELECT DATEDIFF(DAY,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0),DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0)) 235: --借助变量简化
236: SELECT @Date = DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) --本年度第一天 237: SELECT DATEDIFF(DAY,@Date,DATEADD(YEAR,1,@Date)) 238: GO 239:
240: --判断给定日期所在年是否闰年
241: --根据全年总天数判断
242: DECLARE @Date DATETIME; 243: SET @Date = GETDATE() 244: SELECT CASE DATEDIFF(DAY,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0),DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0)) 245: WHEN 365 THEN 平年 ELSE 闰年 END 246: --根据二月天数判断
247: --给日期的上一年最后一天加2个月，即为当年2月最后一天
248: SELECT CASE DAY(DATEADD(MONTH,2,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),-1))) WHEN 28 THEN 平年 ELSE 闰年 END 249: GO 250:
251: --计算给定日期是当年的第几天
252: DECLARE @Date DATETIME; 253: SET @Date = GETDATE() 254: SELECT DATEPART(DAYOFYEAR,@Date) [DayOfYear]; 255: SELECT DATENAME(DAYOFYEAR,@Date) [DayOfYear]; 256: --另一种思路：当前日期与上年最后一天差的天数
257: SELECT DATEDIFF(DAY,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),-1),@Date)[DayOfYear] 258: GO 259:
260: --计算给定日期是当年的第几周
261: DECLARE @Date DATETIME; 262: SET @Date = GETDATE() 263: SELECT DATEPART(WEEK,@Date) [WeekOfYear]; --返回int型 264: SELECT DATENAME(WEEK,@Date) [WeekOfYear]; --返回varchar型 265: GO 266:
267: --计算给定日期是当年的第几月
268: DECLARE @Date DATETIME; 269: SET @Date = GETDATE() 270: SELECT DATEPART(MONTH,@Date) [MonthOfYear]; --返回int型 271: SELECT DATENAME(MONTH,@Date) [MonthOfYear]; --返回varchar型 272: SELECT MONTH(@Date) [MonthOfYear];--返回int型 273: GO 274:
275: --计算给定日期是当年的第几季度
276: DECLARE @Date DATETIME; 277: SET @Date = GETDATE() 278: SELECT DATEPART(QUARTER,@Date) [QuarterOfYear]; --返回int型 279: SELECT DATENAME(QUARTER,@Date) [QuarterOfYear]; --返回varchar型 280: GO 281:
282: --计算给定日期是当月的第几周
283: DECLARE @Date DATETIME; 284: SET @Date = GETDATE() 285: --思路，给定日期是当年的第几周-给定日期所在月第一天是当年的第几周
286: SELECT DATEPART(WEEK,@Date)-DATEPART(WEEK,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0))+1 [WeekOfMonth] 287: SELECT DATEPART(WEEK,@Date)-DATEPART(WEEK,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))+1 [WeekOfMonth] 288: GO 289:
290: --计算给定日期所在月的第一个星期一是哪天
291: DECLARE @Date DATETIME; 292: SET @Date = GETDATE() 293: --思路，1900-01-01(星期一)加上(给定日志所在月的月6号与1900-01-01差的周数)个周
294: --为什么不选7号？如果是7号，那么7好恰好是星期日的话，第一个周一就会算到8号 。
295: --为什么不选5号？如果5号是星期六，那么周一就跑到上月了 。小于5号与这个道理一样 。
296: SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(DAY,6-DATEPART(DAY,@Date),@Date)),0) 所在月的第一个星期一 297: SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(DAY,6-DATEPART(DAY,@Date),@Date)),7) 所在月的第二个星期一 298: SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(DAY,6-DATEPART(DAY,@Date),@Date)),1) 所在月的第一个星期二 299: SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(DAY,6-DATEPART(DAY,@Date),@Date)),8) 所在月的第二个星期二 300: GO
|
