SQL Server 中的时间算法

DECLARE @Date DATETIME

SET @Date=GETDATE()

前一天给定日期的前一天

SELECT DATEADD(DAY@Date) AS 前一天

后一天给定日期的后一天

SELECT DATEADD(DAY@Date) AS 后一天

GO

月初计算给定日期所在月的第一天

这个计算的技巧是先计算当前日期到“”的时间间隔数然后把它加到“”上来获得特殊的日期这个技巧可以用来计算很多不同的日期

DECLARE @Date DATETIME

SET @Date=GETDATE()

SELECT DATEADD(MONTHDATEDIFF(MONTH@Date)) AS 所在月的第一天

精简算法根据SQL Server的时间表示方式可知 可以用代替

SELECT DATEADD(MONTHDATEDIFF(MONTH@Date)) AS 所在月的第一天

上面两种算法精确到天 时分秒均为::

下面算法课以保留时分秒

思路用给定日期减去月第一天与给定日期差的天数

SELECT DATEADD(DAYDATEPART(DAY@Date)@Date)

GO

月末计算给定日期所在月的最后一天

DECLARE @Date DATETIME

SET @Date=GETDATE()

思路当前月的下一月号在减天

SELECT DATEADD(DAYDATEADD(MONTH+DATEDIFF(MONTH@Date))) AS 所在月的最一天

SELECT DATEADD(MONTH+DATEDIFF(MONTH@Date)) AS 所在月的最一天

用代替

SELECT DATEADD(DAYDATEADD(MONTH+DATEDIFF(MONTH@Date))) AS 所在月的最一天

SELECT DATEADD(MONTH+DATEDIFF(MONTH@Date)) AS 所在月的最一天

思路与月初计算思路相同

SELECT DATEADD(MONTHDATEDIFF(MONTH@Date)) AS 所在月的最一天

精简算法 用代替

SELECT DATEADD(MONTHDATEDIFF(MONTH@Date)) AS 所在月的最一天

保留时分秒的算法

SELECT DATEADD(DAYDATEADD(MONTHDATEADD(DAYDATEPART(DAY@Date)@Date)))

GO

其他月计算

计算给定日期所在月的上月第一天

DECLARE @Date DATETIME

SET @Date=GETDATE()

当前月第一天减去一个月

SELECT DATEADD(MONTHDATEADD(MONTHDATEDIFF(MONTH@Date))) AS 上月第一天

简化

SELECT DATEADD(MONTHDATEDIFF(MONTH@Date)) AS 上月第一天

另一种当前月第一天算法

SELECT DATEADD(MONTHDATEADD(DAYDATEPART(DAY@Date)@Date)) 上月第一天

GO

计算给定日期所在月的上月最后一天

DECLARE @Date DATETIME

SET @Date=GETDATE()

当前月第一天减去一天

SELECT DATEADD(DAYDATEADD(MONTHDATEDIFF(MONTH@Date))) AS 上月最后一天

另一种当前月第一天算法

SELECT DATEADD(DAYDATEADD(DAYDATEPART(DAY@Date)@Date)) 上月最后一天

SELECT DATEADD(DAYDATEPART(DAY@Date)@Date) 上月最后一天

另一种算法不能用当前月的最后一天加一个月因为当前月可能是天

例如 SELECT DATEADD(MONTH) 结果是而不是

这也是月末算法采用下月第一天减天计算的原因

但是如果计算月是天择无此问题

例如 SELECT DATEADD(MONTH) 结果是

因此下面算法是正确的 表示 :: SELECT CONVERT(DATETIME)

SELECT DATEADD(MONTHDATEDIFF(MONTH@Date))

另一种当前月算法

SELECT DATEADD(DAYDATEADD(DAYDATEPART(DAY@Date)@Date)) 上月最后一天

简化

SELECT DATEADD(DAYDATEPART(DAY@Date)@Date) 上月最后一天

GO

计算给定日期所在月的下月第一天

DECLARE @Date DATETIME

SET @Date=GETDATE()

当前月第一天加一个月

SELECT DATEADD(MONTHDATEADD(MONTHDATEDIFF(MONTH@Date))) AS 下月第一天

简化

SELECT DATEADD(MONTHDATEDIFF(MONTH@Date)+) AS 下月第一天

另一种当前月第一天算法

SELECT DATEADD(MONTHDATEADD(DAYDATEPART(DAY@Date)@Date)) 下月第一天

GO

计算给定日期所在月的下月最后一天

DECLARE @Date DATETIME

SET @Date=GETDATE()

当前月第一天加个月再减去天

SELECT DATEADD(DAYDATEADD(MONTHDATEADD(MONTHDATEDIFF(MONTH@Date)))) AS 下月最后一天

简化

SELECT DATEADD(DAYDATEADD(MONTHDATEDIFF(MONTH@Date)+)) AS 下月最后一天

SELECT DATEADD(MONTHDATEDIFF(MONTH@Date)+) AS 下月最后一天

另一种算法

SELECT DATEADD(MONTHDATEDIFF(MONTH@Date)+) 下月最后一天

另一种当前月第一天算法

SELECT DATEADD(DAYDATEADD(MONTHDATEADD(DAYDATEPART(DAY@Date)@Date))) 下月最后一天

GO

所在星期的第一天计算给定日期所在星期的第天(星期日为第一天)

DECLARE @Date DATETIME

SET @Date= GETDATE()

与SQL Server语言版本相关的算法

思路当前日期+星期日(每周的第天)与当前日期的差的天数

DATEPART(WEEKDAYDATE)的返回值与@@DATEFIRST相关

SET DATEFIRST 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天)

SELECT DATEADD(WEEKDAYDATEPART(WEEKDAY@Date)@Date) AS 所在星期的第一天星期日

星期日与SQL Server语言版本或@@DATEFIRST无关

是星期日 再加上(当前日期与差的星期数)个星期

SELECT DATEADD(WEEKDATEDIFF(WEEK@Date)) AS 所在星期的星期日

或者

SELECT DATEADD(WEEKDATEDIFF(WEEK@Date)) AS 所在星期的星期日

GO

所在星期的第二天计算给定日期所在星期的第天(星期日为第一天)

DECLARE @Date DATETIME

SET @Date= GETDATE()

思路当前日期+星期一(每周的第天)与当前日期的差的天数

DATEPART(WEEKDAYDATE)的返回值与@@DATEFIRST相关

SET DATEFIRST 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天)

SELECT DATEADD(DAYDATEPART(WEEKDAY@Date)@Date) AS 所在星期的第二天星期一

星期一与SQL Server语言版本或@@DATEFIRST无关

是星期一 再加上(当前日期与差的星期数)个星期

SELECT DATEADD(WEEKDATEDIFF(WEEK@Date)) AS 所在星期的星期一

GO

上个星期第一天计算给定日期所在星期的上一个星期日(星期日为第一天)

DECLARE @Date DATETIME

SET @Date= GETDATE()

思路当前日志所在星期的星期日再减周

DATEPART(WEEKDAYDATE)的返回值与@@DATEFIRST相关

SET DATEFIRST 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天)

SELECT DATEADD(WEEKDATEADD(DAYDATEPART(WEEKDAY@Date)@Date)) AS 上个星期第一天星期日

一周等于天

SELECT DATEADD(DAYDATEADD(DAYDATEPART(WEEKDAY@Date)@Date)) AS 上个星期第一天星期日

简化

SELECT DATEADD(DAYDATEPART(WEEKDAY@Date)@Date) AS 上个星期第一天星期日

上个星期日与SQL Server语言版本或@@DATEFIRST无关

SELECT DATEADD(WEEK+DATEDIFF(WEEK@Date)) AS 上个星期日

或者

SELECT DATEADD(WEEKDATEDIFF(WEEK@Date)) AS 上个星期日

GO

下个星期第一天计算给定日期所在星期的下一个星期日(星期日为第一天)

DECLARE @Date DATETIME

SET @Date= GETDATE()

思路当前日志所在星期的星期日再加周

DATEPART(WEEKDAYDATE)的返回值与@@DATEFIRST相关

SET DATEFIRST 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天)

SELECT DATEADD(WEEKDATEADD(DAYDATEPART(WEEKDAY@Date)@Date)) AS 下个星期第一天星期日

一周等于天

SELECT DATEADD(DAYDATEADD(DAYDATEPART(WEEKDAY@Date)@Date)) AS 下个星期第一天星期日

简化

SELECT DATEADD(DAYDATEPART(WEEKDAY@Date)@Date) AS 下个星期第一天星期日

下个星期日与SQL Server语言版本或@@DATEFIRST无关

SELECT DATEADD(WEEK+DATEDIFF(WEEK@Date)) AS 下个星期日

或者

SELECT DATEADD(WEEKDATEDIFF(WEEK@Date)) AS 下个星期日

GO

判断给定日期是星期几

DECLARE @Date DATETIME

SET @Date= GETDATE()

DATEPART(WEEKDAYDATE)的返回值与@@DATEFIRST相关

SET DATEFIRST 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天)

SELECT DATEPART(WEEKDAY@Date) 返回值 星期日星期一星期二星期六

上面算法与SQL 语言版本或 @@DATEFIRST 相关

下面算法与SQL Server语言版本或@@DATEFIRST无关

SELECT DATENAME(WEEKDAY@Date) 星期

GO

年度计算

DECLARE @Date DATETIME

SET @Date=GETDATE()

年初计算给定日期所在年的第一天

SELECT DATEADD(YEARDATEDIFF(YEAR@Date)) AS 所在年的第一天

年末计算给定日期所在年的最后一天

SELECT DATEADD(YEARDATEDIFF(YEAR@Date)) AS 所在年的最后一天

上一年年初计算给定日期所在年的上一年的第一天

SELECT DATEADD(YEARDATEDIFF(YEAR@Date)) AS 所在年的上一年的第一天

上一年年末计算给定日期所在年的上一年的最后一天

SELECT DATEADD(YEARDATEDIFF(YEAR@Date)) AS 所在年的上一年的最后一天

下一年年初计算给定日期所在年的下一年的第一天

SELECT DATEADD(YEAR+DATEDIFF(YEAR@Date)) AS 所在年的下一年的第一天

下一年年末计算给定日期所在年的下一年的最后一天

SELECT DATEADD(YEAR+DATEDIFF(YEAR@Date)) AS 所在年的下一年的最后一天

GO

季度计算

DECLARE @Date DATETIME

SET @Date=GETDATE()

季度初计算给定日期所在季度的第一天

SELECT DATEADD(QUARTERDATEDIFF(QUARTER@Date)) AS 当前季度的第一天

季度末计算给定日期所在季度的最后一天

SELECT DATEADD(QUARTER+DATEDIFF(QUARTER@Date)) AS 当前季度的最后一天

上个季度初

SELECT DATEADD(QUARTERDATEDIFF(QUARTER@Date)) AS 当前季度的上个季度初

上个季度末

SELECT DATEADD(QUARTERDATEDIFF(QUARTER@Date)) AS 当前季度的上个季度末

下个季度初

SELECT DATEADD(QUARTER+DATEDIFF(QUARTER@Date)) AS 当前季度的下个季度初

下个季度末

SELECT DATEADD(QUARTER+DATEDIFF(QUARTER@Date)) AS 当前季度的下个季度末

计算给定日期所在月的天数

DECLARE @Date DATETIME;

SET @Date = GETDATE()

本月度第一天与下月度第一天所差的天数

SELECT DATEDIFF(DAYDATEADD(MONTHDATEDIFF(MONTH@Date))DATEADD(MONTH+DATEDIFF(MONTH@Date)))

借助变量简化

SELECT @Date = DATEADD(MONTHDATEDIFF(MONTH@Date)) 本月度第一天

SELECT DATEDIFF(DAY@DateDATEADD(MONTH@Date))

另一种思路给定月最后一天的日期记为本月天数

SELECT DAY(DATEADD(MONTHDATEDIFF(MONTH@Date)))

GO

计算给定日期所在季度的天数

DECLARE @Date DATETIME;

SET @Date = GETDATE()

本季度第一天与下季度第一天所差的天数

SELECT DATEDIFF(DAYDATEADD(QUARTERDATEDIFF(QUARTER@Date))DATEADD(QUARTER+DATEDIFF(QUARTER@Date)))

借助变量简化

SELECT @Date = DATEADD(QUARTERDATEDIFF(QUARTER@Date)) 本季度第一天

SELECT DATEDIFF(DAY@DateDATEADD(QUARTER@Date))

GO

计算给定日期所在年度的天数

DECLARE @Date DATETIME;

SET @Date = GETDATE()

本年度第一天与下年度第一天所差的天数

SELECT DATEDIFF(DAYDATEADD(YEARDATEDIFF(YEAR@Date))DATEADD(YEAR+DATEDIFF(YEAR@Date)))

借助变量简化

SELECT @Date = DATEADD(YEARDATEDIFF(YEAR@Date)) 本年度第一天

SELECT DATEDIFF(DAY@DateDATEADD(YEAR@Date))

GO

判断给定日期所在年是否闰年

根据全年总天数判断

DECLARE @Date DATETIME;

SET @Date = GETDATE()

SELECT CASE DATEDIFF(DAYDATEADD(YEARDATEDIFF(YEAR@Date))DATEADD(YEAR+DATEDIFF(YEAR@Date)))

WHEN THEN 平年 ELSE 闰年 END

根据二月天数判断

给日期的上一年最后一天加个月即为当年月最后一天

SELECT CASE DAY(DATEADD(MONTHDATEADD(YEARDATEDIFF(YEAR@Date)))) WHEN THEN 平年 ELSE 闰年 END

GO

计算给定日期是当年的第几天

DECLARE @Date DATETIME;

SET @Date = GETDATE()

SELECT DATEPART(DAYOFYEAR@Date) [DayOfYear];

SELECT DATENAME(DAYOFYEAR@Date) [DayOfYear];

另一种思路当前日期与上年最后一天差的天数

SELECT DATEDIFF(DAYDATEADD(YEARDATEDIFF(YEAR@Date))@Date)[DayOfYear]

GO

计算给定日期是当年的第几周

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

计算给定日期是当年的第几季度

DECLARE @Date DATETIME;

SET @Date = GETDATE()

SELECT DATEPART(QUARTER@Date) [QuarterOfYear]; 返回int型

SELECT DATENAME(QUARTER@Date) [QuarterOfYear]; 返回varchar型

GO

计算给定日期是当月的第几周

DECLARE @Date DATETIME;

SET @Date = GETDATE()

思路给定日期是当年的第几周给定日期所在月第一天是当年的第几周

SELECT DATEPART(WEEK@Date)DATEPART(WEEKDATEADD(MONTHDATEDIFF(MONTH@Date)))+ [WeekOfMonth]

SELECT DATEPART(WEEK@Date)DATEPART(WEEKDATEADD(DAYDATEPART(DAY@Date)@Date))+ [WeekOfMonth]

GO

计算给定日期所在月的第一个星期一是哪天

DECLARE @Date DATETIME;

SET @Date = GETDATE()

思路(星期一)加上(给定日志所在月的月号与差的周数)个周

为什么不选号?如果是号那么好恰好是星期日的话第一个周一就会算到号

为什么不选号?如果号是星期六那么周一就跑到上月了小于号与这个道理一样

SELECT DATEADD(WEEKDATEDIFF(WEEKDATEADD(DAYDATEPART(DAY@Date)@Date))) 所在月的第一个星期一

SELECT DATEADD(WEEKDATEDIFF(WEEKDATEADD(DAYDATEPART(DAY@Date)@Date))) 所在月的第二个星期一

SELECT DATEADD(WEEKDATEDIFF(WEEKDATEADD(DAYDATEPART(DAY@Date)@Date))) 所在月的第一个星期二

SELECT DATEADD(WEEKDATEDIFF(WEEKDATEADD(DAYDATEPART(DAY@Date)@Date))) 所在月的第二个星期二

GO