SQL SERVER 数据库日期算法总括

DECLARE @Date  DATETIME
SET @Date=GETDATE()
–前一天,给定日期的前几日
SELECT DATEADD(DAY,-1,@Date) AS ‘前一天’
–后一天,给定日期的后一天
SELECT DATEADD(DAY,1,@Date) AS ‘后一天’
GO

–月尾,计算给定日期所在月的率后天
–那一个总计的技艺是先计算当前些天期到”1900-01-01″的年月间隔数,然后把它加到”1900-01-01″上来获取格外的日期
–这些技术可以用来统计很多两样的日子
DECLARE @Date  DATETIME
SET @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
–下边算法课以保留时分秒
–思路:用给定日期减去月第一天与给定日期差的运气
SELECT DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)
GO

–月末,计算给定日期所在月的结尾一天
DECLARE @Date  DATETIME
SET @Date=GETDATE()
–思路:当前月的下7月1号在减1天
SELECT DATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,’1900-01-01′,@Date),’1900-01-01′)) AS ‘所在月的最一天’
SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,’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,’1989-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  DATETIME
SET @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 @Date  DATETIME
SET @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
‘上月最终一天’
–另一种算法,不恐怕用当前月的末梢一天加一个月,因为方今月恐怕是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,DATEDIFF(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  DATETIME
SET @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 @Date  DATETIME
SET @Date=GETDATE()
–当前月先是天加2个月再减去1天
SELECT DATEADD(DAY,-1,DATEADD(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)
‘下月最后一天’
–另一种当前月第一天算法
SELECT DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) ‘下月最后一天’
GO

–所在星期的率后天,总结给定日期所在星期的第1天(星期一为第一天)
DECLARE @Date  DATETIME
SET @Date= GETDATE()
–与SQL Server语言版本相关的算法
–思路:当今天期+周日(每一周的第1天)与当下日子的差的小运
–DATEPART(WEEKDAY,DATE)的重返值与@@DATEFIRST相关
SET DATEFIRST 7 —
依然安装为美利坚联邦合众国俄语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
DATEADD(WEEK,DATEDIFF(WEEK,6,@Date),6) AS ‘所在星期的礼拜六’
GO

–所在星期的第二天,计算给定日期所在星期的第2天(星期一为第一天)
DECLARE @Date  DATETIME
SET @Date= GETDATE()
–思路:当前几天子+星期四(每一周的第2天)与近来几天期的差的气数
–DATEPART(WEEKDAY,DATE)的再次来到值与@@DATEFIRST相关
SET DATEFIRST 7 —
仍然安装为美利坚同盟国乌克兰(УКРАЇНА)语SET LANGUAGE us_english; (星期四为率后天)
SELECT DATEADD(DAY,2-DATEPART(WEEKDAY,@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  DATETIME
SET @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(WEEKDAY,@Date),@Date)) AS ‘上个星期第一天,周二’
–简化
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 @Date  DATETIME
SET @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(WEEKDAY,@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

–判断给定日期是星期几
DECLARE @Date  DATETIME
SET @Date= GETDATE()
–DATEPART(WEEKDAY,DATE)的重回值与@@DATEFIRST相关
SET DATEFIRST 7 —
要么设置为米国匈牙利(Magyarország)语SET LANGUAGE us_english; (周三为率后天)
SELECT DATEPART(WEEKDAY,@Date) –返回值
1-星期日,2-星期一,3-星期二……7-星期六
–上边算法与SQL 语言版本或 @@DATEFIRST
相关
–上面算法与SQL
Server语言版本或@@DATEFIRST非亲非故
SELECT DATENAME(WEEKDAY,@Date) ‘星期’
GO

–年度统计
DECLARE @Date  DATETIME
SET @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 ‘所在年的上一年的首后天’
–上一年年末,计算给定日期所在年的上一年的末段一天
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 ‘所在年的下一年的最后一天’
GO

–季度计算
DECLARE @Date  DATETIME
SET @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,@Date)-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
–总结给定日期所在月的气数
DECLARE @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 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)
–本季度第一天
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 DATEDIFF(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个月,即为当年5月最终一天
SELECT
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,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),-1),@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(WEEK,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0))+1
[WeekOfMonth] 
SELECT DATEPART(WEEK,@Date)-DATEPART(WEEK,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))+1
[WeekOfMonth]
GO 
–计算给定日期所在月的首先个星期三是哪一天 
DECLARE @Date DATETIME
SET @Date = GETDATE() 
–思路,1900-01-01(星期日)加上(给定日志所在月的月6号与1900-01-01差的周数)个周
–为何不选7号?即使是7号,那么7好恰好是礼拜日的话,第二个星期二就会算到8号。
–为何不选5号?假若5号是周日,那么礼拜二就跑到上月了。小于5号与这一个道理同样。
SELECT
DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(DAY,6-DATEPART(DAY,@Date),@Date)),0) ‘所在月的第一个周六’ 
SELECT
DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(DAY,6-DATEPART(DAY,@Date),@Date)),7) ‘所在月的第四个星期五’ 
SELECT
DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(DAY,6-DATEPART(DAY,@Date),@Date)),1) ‘所在月的率先个星期一’ 
SELECT
DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(DAY,6-DATEPART(DAY,@Date),@Date)),8) ‘所在月的首个星期一’
GO

相关文章