2018年3月24日 星期六

DATEADD 及ADD_MONTHS日期運算函數之差異比較

透過日期資料加減運算,可將日期資料延後或提前。SQL SERVER提供DATEADD()函數,可針對各種時間區間單位(如年、月、日),以增加或減少整數單位數,進行日期單位增減運算。ORACLE則提供以月為增減整數單位的ADD_MONTHS函數,ORACLE 9遵循SQL 99提供INTERVAL型態,藉此可達成各種日期或混合單位的運算,TO_YMINTERVAL函數則是提供特定年月字串轉換為日期型態功能。

SQL SERVER所提供的DATEADD()函數,第一個引數為時間間隔單位(Interval),用以指定增、減運算的時間間隔單位,常用參數整理如下表。第二個引數為增、減時間間隔單位的次數(Count),此值可為正數(未來),或負數(過去),但需特別注意,此值可接受浮點數,當指定的數值不是整數時,系統會自動捨棄值小數部分。第三個引數為基準日期(Date),將基準日期加上Count次數的Interval後,即為傳回值之日期。
Interval
縮寫
yyyyyy
qqq
mmm
ddd
wkww
hh
min
sss
毫秒
ms

ORACLE對日期加減運算提供以月為增減單位的ADD_MONTHS函數。至於其他日期單位如週、日、小時、分、秒均是以「天」為單位進行加減運算,「季」則可轉換為以月為單位的計算,而「年」則可採用TO_YMINTERVAL函數處理。

在此將兩種資料庫對「月」為單位的日期加減運算函數之差異,在此特別以2016-01-30(非月底)、2009-01-31(月底)、2016-02-28(月底)及2016-04-30(月底)四筆基準日期,分別增加12個月,以說明兩函數應用上的異同點。SQL語去及執行結果如下所示:
SQL SERVER
ORACLE
SELECT Target
    , "月底"
    , DATEADD(M, 1, Target)    "M+1"
    , DATEADD(M, 2, Target)    "M+2"
FROM
  (
  SELECT '2016-01-30' Target, '' "月底"
  UNION ALL
  SELECT '2016-01-31',   ''
  UNION ALL
  SELECT '2016-02-28',   ''
  UNION ALL
  SELECT '2016-04-30',   ''
  ) A
SELECT Target
    , "月底"
    , ADD_MONTHS(Target, 1)   "M+1"
    , ADD_MONTHS(Target, 2)    "M+2"
FROM
  (
  SELECT DATE '2016-01-30' Target, '' "月底"
  FROM DUAL
  UNION ALL
  SELECT DATE '2016-01-31',   ''
  FROM DUAL
  UNION ALL
  SELECT DATE '2016-02-28',   ''
  FROM DUAL
  UNION ALL
  SELECT DATE '2016-04-30',   ''
  FROM DUAL
  )

SQL執行結果如下表。
(1)  對於非月底日的2016-01-30增加12個月兩種測試結果均相同。

但月底日的三筆測試,兩個函數的執行結果則有所差異(以字註記)
(2)  2016-01-31測試,兩者相同結果;
(3)  2016-02-29為例,DATEADD函數增加12個月結果均為28日,但ADD_MONTHS運算則月底日即03-3104-30
(4)  2016-04-30為例,DATEADD的結果為05-3006-30,而ADD_MONTHS運算仍為月底日即05-3106-30

SQL SERVER(DATEADD)

ORACLE(ADD_MONTHS)
Target
月底
M+1
M+2
2016-01-30
02-29
03-30
2016-01-31
02-29
03-31
2016-02-29
03-29
04-29
2016-04-30
05-30
06-30

Target
月底
M+1
M+2
2016-01-30
02-29
03-30
2016-01-31
02-29
03-31
2016-02-29
03-31
04-30
2016-04-30
05-31
06-30

將兩個函數之Pseudo Code整理如下表:

SQL SERVER(DATEADD)
ORACLE(ADD_MONTHS)
Pseudo Code
1.  目標月份的同一天日期是否存在
存在(合法)則直接傳回
2.  若不存在則傳回最後一天(與ORACLE不為月底的定義相同)

IF 判斷基準日期為月底日 THEN
則目標月份都是傳回的最後一天
ELSE 若基準日期不為月底
則傳回目標月份的同一天日期,若不存在則傳回最後一天。


ADD_MONTHS(ORACLE)DATEADD(MSSQL)函數於月份運算差異分析

基準日期(月底日
基準日期(非月底日)
DATEADD

目標日期
MIN(月底日,同一日)
目標日期
MIN(月底日,同一日)
ADD_MONTHS
目標日期
月底日
目標日期
MIN(月底日,同一日)




沒有留言:

張貼留言