[日期函數]日期/時間加減運算

將日期/時間延後或提前。

指令說明:

ORACLE

依功能可分成三類:

1.       基本函數

提供以月為增減整數單位的ADD_MONTHS函數。

2.    時間間隔函數

遵循SQL99提供INTERVAL資料型態，即達成各種日期或混合單位的運算，時間間隔函數共有四個其中TO_YMINTERVAL及TO_DSINTERVAL函數可將年月/日時分秒字串轉換為時間間隔；NUMTOYMINTERVAL及NUMTODSINTERVAL則是指定數量(可為浮點數)之間格單位計算出算所得。

3.       INTERVAL資料型態

YEAR TO MONTH及DAY TO SECOND兩種型式的INTERVAL資料型態。

語法	說明
ADD_MONTHS(date, count)	將指定幾個月增減至日期時間
NUMTOYMINTERVAL(x, 'interval_unit')	指定數量(可為浮點數)間格單位
NUMTODSINTERVAL(x, 'interval_unit')	指定數量(可為浮點數)間格單位
TO_YMINTERVAL('[+|-]years-months)	時間間隔函數，將字串轉換為時間間隔
TO_DSINTERVAL('[+|-]days-seconds)	時間間隔函數，將字串轉換為時間間隔
INTERVAL 'time_expr' YEAR[(year_precision)] TO MONTH	SQL99所定義日期型態(YEAR TO MONTH)
INTERVAL 'time_expr' DAY[(day_precision)] TO SECOND[(fractional_second_precision)]	SQL 99所定義日期型態(DAY TO SECOND)

範例:

SELECT DT --(0) 2020-01-01 2020-01-01    , DT +TO_YMINTERVAL('01-06') --(1) 1年6個月 2021-07-01    , DT + INTERVAL '120-3' YEAR(3) TO MONTH --(2) 增加120年3天 2040-04-01    , DT + INTERVAL '120'    YEAR(3) --(3) 120年 2140-01-01    , DT + INTERVAL '120' MONTH --(4) 增加120月 2030-01-01    -----------------    , DT + INTERVAL '11 10:09:08.555' DAY TO SECOND(3) --(5) 11天10:09:08.0555 2020-01-12 10:09:08    , DT + INTERVAL '100 10' DAY(3) TO HOUR --(6) 100天10小時 2020—04-10 10:00:00    -----------------     , DT + INTERVAL '999' DAY(3) --(8) 999天 2022-09-26    , DT + INTERVAL '15.6789' SECOND(2,3) --(11) 15.679秒 2020-01-01 00:00:15 FROM ( SELECT DATE'2020-01-01' DT FROM DUAL )

MSSQL

MSSQL提供DATEADD()函數，可針對各種時間區間單位（如年、季、月、日、時、分及秒）以增加或減少整數單位數(如具小數自動捨位)，進行時間區間單位增減運算。

語法	說明
DATEADD (Interval , count, date)	- 1st: 時間間隔單位（Interval）: 年月日時分秒 - 2nd: 增減時間間隔單位的次數（Count） - 3rd: 基準日期時間

第一個引數為時間間隔單位（Interval），用以指定增、減運算的時間間隔單位，常用參數整理如下表。

第二個引數為增、減時間間隔單位的次數（Count），此值可為正數（未來），或負數（過去），但需特別注意，此值可接受浮點數，當指定的數值不是整數時，系統會自動捨棄值小數部分。

第三個引數為基準日期（Date），將基準日期加上Count次數的Interval後，即為傳回值之日期。

Interval	縮寫
年	yy、yyyy
季	qq、q
月	mm、m
日	dd、d
週	wk、ww
時	hh
分	mi、n
秒	ss、s
毫秒	ms

指令比較:

移除字串(str)前後空白(或特定字元)。比較如下:

	ORACLE	MSSQL
語法	ADD_MONTHS(date, count)	DATEADD(Interval , count, date)
支援年/季/月加減	ü(如月底日時則特殊處理)	ü
日、時分秒加滅	û	ü(支援)
月數(count)捨位	ü(小數位捨位)	ü(小數位捨位)
年、月 (資料型態)	INTERVAL 'time_expr' YEAR[(year_precision)] TO MONTH	û (不支援，僅部分支援)
日、時、分、秒 (資料型態)	INTERVAL 'time_expr' DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)]	û (不支援，僅部分支援)

範例:

	ORACLE	MSSQL
延後12個月	SELECT ADD_MONTHS(sysdate, 12) FROM DUAL	SELECT DATEADD(mm, 12, getdate())
提前30分鐘	SELECT sysdate - 30/60/24 , sysdate - 30 * INTERVAL '1' MINUTE , sysdate + INTERVAL '-30' MINUTE FROM DUAL	SELECT getdate() - 30.0/60/24    , DATEADD(mi, -30, getdate()) 註: 使用運算方式時，需注意數值運算特性。
提前300天	SELECT sysdate - 300   , sysdate + INTERVAL '-300' DAY(3) FROM DUAL	SELECT getdate() – 300    , DATEADD(dd, -300, getdate())

由於ORACLE提供功能繁多，無法一一說明，建議可自行參閱官方文件。另外，ORACLE的月份增減運算時，請務必限制，當使用INTERVAL時，由於系統並不會先驗證日期合法性，如異常則將出現ORA-01839錯誤，建議月份增減運算時使用ADD_MONTHS函數，避免不必要困擾。

SQL	說明
SELECT ADD_MONTHS(DATE'2020-01-31', 1)  --ORA-01839: date not valid for month specified  --, 01/31增加一個月為02/31(日期不合法)，以下三者均異常    --, DATE'2020-01-31' + NUMTOYMINTERVAL(1, 'MONTH')  --, DATE'2020-01-31' + TO_YMINTERVAL('00-01')  --, DATE'2020-01-31' + INTERVAL '1' MONTH       FROM DUAL	月份增減運算，建議使用ADD_MONTHS函數

建議延伸閱讀：

函數	資料庫	延伸閱讀
		DATEADD 及ADD_MONTHS日期運算函數之差異比較