{最後修訂: [2020-06-14]增加FORMAT函數/EOMONTH函數}
對於時間區間報表或一些運算需要，需得到今天日期(不含時分秒）、月初、月底、季初等其他特殊時點以供計算參考使用，本文將整理表列以供參考。
運算時所參考之日期欄位資料或系統時間，MSSQL及ORACLE分別使用GETDATE()及SYSDATE，其內容包含時、分、秒等資訊，為運算上便利性及必要性應先行捨去，ORALE提供TRUNC及ROUND兩個函數可指定截斷格式（年、季、月、日、時、分等)以進行各種日期單位截斷，也可採用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)小數部分(時分秒，將轉換為天)
| |||||||||||||||||||||||||||||||||||||
日期資料可區分為「日期」(整數)及「時分秒」(小數)兩部分。
以下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
| ||||||||||
結果
|
|
可使用FLOOR或ROUND函數捨位運算將小數位捨去可得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
以當行月底/月初運算時，當使用DATEADD或ADD_MONTHS計算出其他月份之月底日期時，請特別注意ORACLE/MSSQL函數上之差異，建議以月初日進行推算，下列MSSQL/ORACLE第一組SQL，如下，其餘自行套用，另外，FORMAT、EOMONTH為MSSQL2012以上版本提供。
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
|
|
|
|
|
沒有留言:
張貼留言