程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server 中的時間算法

SQL Server 中的時間算法

編輯:關於SqlServer

      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()

      --思路:當前月的下一月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 -- 或者設置為美國英語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 '當前季度的下個季度末'

      --計算給定日期所在月的天數

      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個月,即為當年2月最後一天

      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

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved