每日一SQL-善用DATEADD和DATEDIFF

每日一SQL-善用DATEADD和DATEDIFF

上個星期去Tech-Day聽了幾場有趣的課,其中一堂是楊志強老師的

"深入了解T-SQL",講的很棒,雖然是第一堂八點四十的課,但講得很精采,讓我越聽越有精神。

今天來寫一個當天的範例,應該很多人看過,但我覺得語法滿有趣的。

用DATEADD及DATEDIFF組合起來,可以算出一季或是一個月的最後一天等等…

 

首先先分別來說明DATEADD及DATEDIFF

DATEDIFF是算兩個日期間的間隔,傳回帶正負號的整數

DATEDIFF ( datepart , startdate , enddate )

datepart為間隔的單位,startdate跟enddate應該看字面的意思就知道了吧。

因此如果語法寫

SELECT DATEDIFF(DAY, '2010-10-03','2010-10-04'  )

出來的結果就是 1,代表相隔一天。

 

DATEADD是計算某日期加上一個數值,傳回的日期

DATEADD (datepart , number , date )

datepart一樣是單位,number是指定的數值,date是要被加上的原始日期

SELECT DATEADD(MONTH,2,'2010-10-06')

傳回的結果是2010-12-06 00:00:00.000

 

講解完這兩個函數使用之後,接著開始解釋某月的第一天怎麼計算

不過還是要再解釋一個東西

如果今天將0轉為datetime,如 select CONVERT(datetime,0,112)

出來的結果會是1900-01-01 00:00:00.000

因此我們將會用這個數值為基準

首先先計算今天到1900-01-01相差幾個月

語法是

SELECT DATEDIFF(MONTH,0,GETDATE())

算出來的數值是1329,代表相差了1329個月

再來將 1900-01-01加上1329個月,結果出來就是這個月的第一天了!

SELECT  DATEADD(MONTH,  DATEDIFF(MONTH,0,GETDATE()) ,0)

image

 

結果:2010-10-01 00:00:00.000

 

用這個方法就可以算出很多種不同的結果

例如如果是每個月最後一天的話,就是

SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE() ), -1)

原理是先算出今天跟 1899-12-31的月份差距( -1代表1899-12-31,-2就是1899-12-30依此類推)

接著再將1899-12-31加上月份差距,答案就是2010-10-31 00:00:00.000

 

如果是當季的最後一天,如下

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, -1,GETDATE() ), -1)

 

還有很多運用,就看自己怎麼發揮囉。

 

參考連結:

我的Coding之路-每日一SQL-善用DATEADD和DATEDIFF

保哥-如何用簡單的 SQL 技巧取得特定日期是否為週末假日