SQL Server 中的时间算法

1、SQL Server 中的时间算法DECLARE Date  DATETIMESET Date=GETDATE()-前一天，给定日期的前一天SELECT DATEADD(DAY,-1,Date) AS '前一天'-后一天，给定日期的后一天 SELECT DATEADD(DAY,1,Date) AS '后一天'GO-月初，计算给定日期所在月的第一天-这个计算的技巧是先计算当前日期到“1900-01-01”的时间间隔数，然后把它加到“1900-01-01”上来获得特殊的日期，这个技巧可以用-来计算很多不同的日期。DECLARE Date  DAT

2、ETIMESET Date=GETDATE()SELECT DATEADD(MONTH,DATEDIFF(MONTH,'1900-01-01',Date),'1900-01-01') AS '所在月的第一天'-精简算法，根据SQL Server的时间表示方式可知，'1900-01-01' 可以用0代替SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,Date),0) AS '所在月的第一天'-上面两种算法精确到天 时分秒均为00:00:00.000-下面算法课以保留时分秒-思路：用给定日期

3、减去月第一天与给定日期差的天数SELECT DATEADD(DAY,1-DATEPART(DAY,Date),Date)GO-月末，计算给定日期所在月的最后一天DECLARE Date  DATETIMESET Date=GETDATE()-思路：当前月的下一月1号在减1天SELECT DATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,'1900-01-01',Date),'1900-01-01') AS '所在月的最一天'SELECT DATEADD(MONTH,1+DATEDIFF(MONT

4、H,'1900-01-01',Date),'1900-01-01')-1 AS '所在月的最一天'-1900-01-01 用0代替SELECT DATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,Date),0) AS '所在月的最一天'SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,0,Date),0)-1 AS '所在月的最一天'-思路：与月初计算思路相同SELECT DATEADD(MONTH,DATEDIFF(MONTH,'19

5、89-12-31',Date),'1989-12-31') AS '所在月的最一天'-精简算法，'1989-12-31' 用-1代替SELECT DATEADD(MONTH,DATEDIFF(MONTH,-1,Date),-1) AS '所在月的最一天'-保留时分秒的算法SELECT DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date)GO-其他月计算-计算给定日期所在月的上月第一天DECLARE Date  DATETIMES

6、ET Date=GETDATE()-当前月第一天减去一个月SELECT DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,Date),0) AS '上月第一天'-简化SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,Date)-1,0) AS '上月第一天'-另一种当前月第一天算法SELECT DATEADD(MONTH,-1,DATEADD(DAY,1-DATEPART(DAY,Date),Date) '上月第一天'GO-计算给定日期所在月的上月最后一天DECLARE Da

7、te  DATETIMESET Date=GETDATE()-当前月第一天减去一天SELECT DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,Date),0) AS '上月最后一天'-另一种当前月第一天算法SELECT DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,Date),Date) '上月最后一天'SELECT DATEADD(DAY,1-DATEPART(DAY,Date),Date)-1 '上月最后一天'-另一种算法，不能用当前月的最后一天加一

8、个月，因为当前月可能是30天。-例如 SELECT DATEADD(MONTH,1,'2010-06-30') -结果是2010-07-30而不是2010-07-31，-这也是月末算法采用下月第一天减1天计算的原因-但是如果计算月是31天择无此问题-例如 SELECT DATEADD(MONTH,1,'2010-05-31') -结果是2010-06-30-因此下面算法是正确的，-1 表示'1899-12-31 00:00:00.000'- SELECT CONVERT(DATETIME,-1) SELECT DATEADD(MONTH,DATE

9、DIFF(MONTH,-1,Date)-1,-1)-另一种当前月算法SELECT DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,Date),Date) '上月最后一天'-简化SELECT DATEADD(DAY,0-DATEPART(DAY,Date),Date) '上月最后一天'GO-计算给定日期所在月的下月第一天DECLARE Date  DATETIMESET Date=GETDATE()-当前月第一天加一个月SELECT DATEADD(MONTH,1,DATEADD(MONTH,DATEDIFF(MONT

10、H,0,Date),0) AS '下月第一天'-简化SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,Date)+1,0) AS '下月第一天'-另一种当前月第一天算法SELECT DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date) '下月第一天'GO-计算给定日期所在月的下月最后一天DECLARE Date  DATETIMESET Date=GETDATE()-当前月第一天加2个月再减去1天SELECT DATEADD(DAY,-1,DATEADD(

11、MONTH,2,DATEADD(MONTH,DATEDIFF(MONTH,0,Date),0) AS '下月最后一天'-简化SELECT DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,Date)+2,0) AS '下月最后一天'SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,Date)+2,0)-1 AS '下月最后一天'-另一种算法SELECT DATEADD(MONTH,DATEDIFF(MONTH,-1,Date)+1,-1) '下月最后一天'-另一种

12、当前月第一天算法SELECT DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,Date),Date) '下月最后一天'GO-所在星期的第一天，计算给定日期所在星期的第1天(星期日为第一天) DECLARE Date  DATETIMESET Date= GETDATE()-与SQL Server语言版本相关的算法-思路：当前日期+星期日(每周的第1天)与当前日期的差的天数-DATEPART(WEEKDAY,DATE)的返回值与DATEFIRST相关SET DATEFIRST 7 - 或者设置为美国英语

13、SET LANGUAGE us_english; (星期日为第一天)SELECT DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,Date),Date) AS '所在星期的第一天，星期日'-星期日，与SQL Server语言版本或DATEFIRST无关-'1989-12-31' 是星期日，'1989-12-31' 再加上(当前日期与1989-12-31差的星期数)个星期SELECT DATEADD(WEEK,DATEDIFF(WEEK,-1,Date),-1) AS '所在星期的星期日'-或者SELECT D

14、ATEADD(WEEK,DATEDIFF(WEEK,6,Date),6) AS '所在星期的星期日'GO-所在星期的第二天，计算给定日期所在星期的第2天(星期日为第一天)DECLARE Date  DATETIMESET Date= GETDATE()-思路：当前日期+星期一(每周的第2天)与当前日期的差的天数-DATEPART(WEEKDAY,DATE)的返回值与DATEFIRST相关SET DATEFIRST 7 - 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天)SELECT DATEADD(DAY,2-DATEPART(W

15、EEKDAY,Date),Date) AS '所在星期的第二天，星期一'-星期一，与SQL Server语言版本或DATEFIRST无关-'1900-01-01' 是星期一，'1900-01-01' 再加上(当前日期与1900-01-01差的星期数)个星期SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,Date),0) AS '所在星期的星期一'GO-上个星期第一天，计算给定日期所在星期的上一个星期日(星期日为第一天)DECLARE Date  DATETIMESET Date= GETDATE(

16、)-思路：当前日志所在星期的星期日再减1周-DATEPART(WEEKDAY,DATE)的返回值与DATEFIRST相关SET DATEFIRST 7 - 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天)SELECT DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS '上个星期第一天，星期日'-一周等于7天SELECT DATEADD(DAY,-7,DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS '上个星期第一天，星

17、期日'-简化SELECT DATEADD(DAY,-6-DATEPART(WEEKDAY,Date),Date) AS '上个星期第一天，星期日'-上个星期日，与SQL Server语言版本或DATEFIRST无关SELECT DATEADD(WEEK,-1+DATEDIFF(WEEK,-1,Date),-1) AS '上个星期日'-或者SELECT DATEADD(WEEK,DATEDIFF(WEEK,6,Date),-1) AS '上个星期日'GO-下个星期第一天，计算给定日期所在星期的下一个星期日(星期日为第一天)DECLARE D

18、ate  DATETIMESET Date= GETDATE()-思路：当前日志所在星期的星期日再加1周-DATEPART(WEEKDAY,DATE)的返回值与DATEFIRST相关SET DATEFIRST 7 - 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天)SELECT DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS '下个星期第一天，星期日'-一周等于7天SELECT DATEADD(DAY,7,DATEADD(DAY,1-DATEPART(WEE

19、KDAY,Date),Date) AS '下个星期第一天，星期日'-简化SELECT DATEADD(DAY,8-DATEPART(WEEKDAY,Date),Date) AS '下个星期第一天，星期日'-下个星期日，与SQL Server语言版本或DATEFIRST无关SELECT DATEADD(WEEK,1+DATEDIFF(WEEK,-1,Date),-1) AS '下个星期日'-或者SELECT DATEADD(WEEK,DATEDIFF(WEEK,-1,Date),6) AS '下个星期日'GO-判断给定日期是星期几D

20、ECLARE Date  DATETIMESET Date= GETDATE()-DATEPART(WEEKDAY,DATE)的返回值与DATEFIRST相关SET DATEFIRST 7 - 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天)SELECT DATEPART(WEEKDAY,Date) -返回值 1-星期日，2-星期一，3-星期二.7-星期六-上面算法与SQL 语言版本或 DATEFIRST 相关-下面算法与SQL Server语言版本或DATEFIRST无关SELECT DATENAME(WEEKDAY,Date) '星期

21、' GO-年度计算DECLARE Date  DATETIMESET Date=GETDATE()-年初，计算给定日期所在年的第一天SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,Date),0) AS '所在年的第一天'-年末，计算给定日期所在年的最后一天SELECT DATEADD(YEAR,DATEDIFF(YEAR,-1,Date),-1) AS '所在年的最后一天'-上一年年初，计算给定日期所在年的上一年的第一天SELECT DATEADD(YEAR,DATEDIFF(YEAR,-0,Date)-1,0) AS

22、 '所在年的上一年的第一天'-上一年年末，计算给定日期所在年的上一年的最后一天SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,Date),-1) AS '所在年的上一年的最后一天'-下一年年初，计算给定日期所在年的下一年的第一天SELECT DATEADD(YEAR,1+DATEDIFF(YEAR,0,Date),0) AS '所在年的下一年的第一天'-下一年年末，计算给定日期所在年的下一年的最后一天SELECT DATEADD(YEAR,1+DATEDIFF(YEAR,-1,Date),-1) AS '所在年的下

23、一年的最后一天'GO-季度计算DECLARE Date  DATETIMESET Date=GETDATE()-季度初，计算给定日期所在季度的第一天SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,Date),0) AS '当前季度的第一天'-季度末，计算给定日期所在季度的最后一天SELECT DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,Date),-1) AS '当前季度的最后一天'-上个季度初SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,D

24、ate)-1,0) AS '当前季度的上个季度初'-上个季度末SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,Date),-1) AS '当前季度的上个季度末'-下个季度初SELECT DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,Date),0) AS '当前季度的下个季度初'-下个季度末SELECT DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,Date),-1) AS '当前季度的下个季度末'GO-计算给定日期所在月的天数DECLAR

25、E Date DATETIME;SET Date = GETDATE()-本月度第一天与下月度第一天所差的天数SELECT DATEDIFF(DAY,DATEADD(MONTH,DATEDIFF(MONTH,0,Date),0),DATEADD(MONTH,1+DATEDIFF(MONTH,0,Date),0)-借助变量简化SELECT Date = DATEADD(MONTH,DATEDIFF(MONTH,0,Date),0) -本月度第一天SELECT DATEDIFF(DAY,Date,DATEADD(MONTH,1,Date)-另一种思路：给定月最后一天的日期，记为本月天数SELECT

26、 DAY(DATEADD(MONTH,DATEDIFF(MONTH,-1,Date),-1)GO-计算给定日期所在季度的天数DECLARE Date DATETIME;SET Date = GETDATE()-本季度第一天与下季度第一天所差的天数SELECT DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,Date),0),DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,Date),0)-借助变量简化SELECT Date = DATEADD(QUARTER,DATEDIFF(QUARTER,0,Date),0) -本季

27、度第一天SELECT DATEDIFF(DAY,Date,DATEADD(QUARTER,1,Date)GO-计算给定日期所在年度的天数DECLARE Date DATETIME;SET Date = GETDATE()-本年度第一天与下年度第一天所差的天数SELECT DATEDIFF(DAY,DATEADD(YEAR,DATEDIFF(YEAR,0,Date),0),DATEADD(YEAR,1+DATEDIFF(YEAR,0,Date),0)-借助变量简化SELECT Date = DATEADD(YEAR,DATEDIFF(YEAR,0,Date),0) -本年度第一天SELECT D

28、ATEDIFF(DAY,Date,DATEADD(YEAR,1,Date)GO-判断给定日期所在年是否闰年-根据全年总天数判断DECLARE Date DATETIME;SET Date = GETDATE()SELECT CASE DATEDIFF(DAY,DATEADD(YEAR,DATEDIFF(YEAR,0,Date),0),DATEADD(YEAR,1+DATEDIFF(YEAR,0,Date),0) WHEN 365 THEN '平年' ELSE '闰年' END -根据二月天数判断-给日期的上一年最后一天加2个月，即为当年2月最后一天SELECT

29、CASE DAY(DATEADD(MONTH,2,DATEADD(YEAR,DATEDIFF(YEAR,0,Date),-1) WHEN 28 THEN '平年' ELSE '闰年' END GO-计算给定日期是当年的第几天DECLARE Date DATETIME;SET Date = GETDATE()SELECT DATEPART(DAYOFYEAR,Date) DayOfYear;SELECT DATENAME(DAYOFYEAR,Date) DayOfYear;-另一种思路：当前日期与上年最后一天差的天数SELECT DATEDIFF(DAY,DATE

30、ADD(YEAR,DATEDIFF(YEAR,0,Date),-1),Date)DayOfYearGO-计算给定日期是当年的第几周DECLARE Date DATETIME;SET Date = GETDATE()SELECT DATEPART(WEEK,Date) WeekOfYear; -返回int型SELECT DATENAME(WEEK,Date) WeekOfYear; -返回varchar型GO-计算给定日期是当年的第几月DECLARE Date DATETIME;SET Date = GETDATE()SELECT DATEPART(MONTH,Date) MonthOfYear; -返回int型SELECT DATENAME(MONTH,Date) MonthOfYear; -返回varchar型SELECT MONTH(Date) MonthOfYear;-返回int型GO-计算给定日期是当年的第几季度DECLA