2013年9月30日 星期一

取得特定日期

{最後修訂: [2020-06-14]增加FORMAT函數/EOMONTH函數}
對於時間區間報表或一些運算需要,需得到今天日期(不含時分秒)、月初、月底、季初等其他特殊時點以供計算參考使用,本文將整理表列以供參考。

運算時所參考之日期欄位資料或系統時間,MSSQLORACLE分別使用GETDATE()SYSDATE,其內容包含時、分、秒等資訊,為運算上便利性及必要性應先行捨去,ORALE提供TRUNCROUND兩個函數可指定截斷格式(年、季、月、日、時、分等)以進行各種日期單位截斷,也可採用TO_CHAR轉換函數,根據所指定日期格式轉換為所需的部分日期字串;MSSQL並無提供日期截斷函數,應採用運算方式,2012以上版本則可嘗試採用FORMAT函數。

MSSQL預設運算基準日期為1900-01-01 00:00:00,後續相關日期運算可採用相對於基準日期之差異以轉換方式取得;ORACLE則並無運算基準日期概念,但可用日期截斷及相關函數即可,時分秒轉換運算概念相同。以下將以MSSQL為例,針對2013-09-28 23:12:04進行日期截斷說明。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
日期
2
0
1
3
-
0
9
-
2
8
2
3
:
1
2
:
0
4
數值
41543
.
966712962960
計算
(1)整數部分(日期)
相對1900-01-01(基準日期)
SELECT DATEADD(DD, 41543, 0)
---------------------------
結果: 2013-09-28

: 3rd參數基準時間為0,
即代表1900-01-01


(2)小數部分(時分秒,將轉換為天)
轉換成天
計算
23
0.958333333333
=23/24
12
0.008333333333
=12/60/24
4
0.000046296296
=4/60/60/24
0.966712962963
日期資料可區分為「日期」(整數)及「時分秒」(小數)兩部分。

以下SQL及結果中將逐步說明運算方法及概念,可將日期資料轉換為數值資料(或浮點數)將得到41543.966712962960數值。

SQL /結果
SQL
SELECT DT
, CAST(DT AS NUMERIC(20,12))       "DT(float)"
     , FLOOR(CAST(DT AS NUMERIC(20,12)))      "DT(int)"
     , CAST(FLOOR(CAST(DT AS NUMERIC(20,12))) AS datetime) "TRUNC(DT)"
FROM
     (
     SELECT CAST('2013-09-28 23:12:04' as datetime) DT
    ) A
結果

DT
DT(float)
DT(int)
TRUNC(DT)
1
2013-09-28 23:12:04
41543.96671296296
41543
2013-09-28 00:00:00
可使用FLOORROUND函數捨位運算將小數位捨去可得41543,再將此數值資料轉換為日期型態即可得到「2013-09-28 00:00:00」,由前述可得知午夜0點的日期資料轉換成數值時,為不具有小數位數整資料,將此種型式的日期命名為「整數日」。

另外,ORACLE提供LAST_DAY函數(月底)、日期截斷運算TRUNC函數,可簡便方式取各種形式日期、時間等資訊;MSSQL2012提供EOMONTH函數(月底),但並未提供類似之截斷函數,但可採用轉換、運算的方法來達成。

以下列出常用特定時間運算SQL語法:
 
MSSQL
ORACLE
整數日
(當天)
D
DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)
TRUNC(SYSDATE)
C
FORMAT(GETDATE(), 'yyyy-MM-dd')
CONVERT(CHAR(10), GETDATE(), 120)
TO_CHAR(SYSDATE, 'YYYY-MM-DD')
月初
D
DATEADD(MM, DATEDIFF(MM,0, GETDATE()), 0)
TRUNC(SYSDATE, 'MM')
C
FORMAT(GETDATE(), 'yyyy-MM-01')
CONVERT(CHAR(7), GETDATE(), 120) + '-01'
TO_CHAR(SYSDATE, 'YYYY-MM-"01"')
下月初
D
DATEADD(MM, DATEDIFF(MM, 0, GETDATE())+1, 0)
LAST_DAY(SYSDATE)+1
C
FORMAT(DATEADD(MM, 1, GETDATE()), 'yyyy-MM-01')
DATEADD(MM,1,CONVERT(CHAR(7),GETDATE(),120)+'-01')
TO_CHAR(ADD_MONTHS(SYSDATE, 1)
, 'YYYY-MM-"01"')
月底
D
EOMONTH(GETDATE())
DATEADD(MM, DATEDIFF(MM,0,GETDATE())+1, 0) -1
LAST_DAY(SYSDATE)
C
FORMAT(EOMONTH(GETDATE()), 'yyyy-MM-dd')
DATEADD(MM,1,CONVERT(CHAR(7),GETDATE(),120)+'-01')-1
年初
D
CAST(FORMAT(GETDATE(), 'yyyy-01-01') AS DATETIME)
DATEADD(YY, DATEDIFF(YY,0,GETDATE()), 0)
TRUNC(SYSDATE, 'YY')
C
FORMAT(GETDATE(), 'yyyy-01-01')
CONVERT(CHAR(4), GETDATE(), 120) + '-01-01'
TO_CHAR (SYSDATE, 'YYYY-"01-01"')
年底
D
CAST(FORMAT(GETDATE(), 'yyyy-12-31) AS DATETIME)
DATEADD(YY, DATEDIFF(YY, 0, GETDATE())+1, 0)-1
C
FORMAT(GETDATE(), 'yyyy-12-31')
CONVERT(CHAR(4), GETDATE(), 120) + '-12-31'
TO_CHAR(SYSDATE, 'YYYY-"12-31"')
本週三
D
DATEADD(WK, DATEDIFF(WK, 0, GETDATE()), 0)+2
TRUNC(SYSDATE,'DAY')+3
C
季初
D
DATEADD(Q, DATEDIFF(Q, 0, GETDATE()), 0)
TRUNC(SYSDATE, 'Q')
: TO_CHAR取得是Nth季,非日期。
整點
D
DATEADD(HH, DATEDIFF(HH, 0, GETDATE()), 0)
TRUNC(SYSDATE, 'HH24')
C
FORMAT(GETDATE(), 'yyyy-MM-dd HH')
分鐘
D
DATEADD(MI, DATEDIFF(MI, 0, GETDATE()), 0)
TRUNC(SYSDATE, 'MI')
C
FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm')
D:Date, C:Char

以當行月底/月初運算時,當使用DATEADDADD_MONTHS計算出其他月份之月底日期時,請特別注意ORACLE/MSSQL函數上之差異,建議以月初日進行推算,下列MSSQL/ORACLE第一組SQL,如下,其餘自行套用,另外,FORMATEOMONTHMSSQL2012以上版本提供。
 
MSSQL
ORACLE
整數日
(當天)
D
SELECT
DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)
SELECT TRUNC(SYSDATE)
FROM DUAL
C
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd')
, CONVERT(CHAR(10), GETDATE(), 120)
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD')
FROM DUAL


建議延伸閱讀文章:
函數
資料庫
延伸閱讀
FORMAT
MSSQL2012

MSSQL2012-2017