取得兩個日期（時間）的差距。
可直接將兩個日期進行減法運算(d1- d2)，即可得到兩個日期的差距天數，ORACLE將傳回具有浮點數的天數，MSSQL則會將時間差轉換為相對於基準日期（1900-01-01 00:00:00）的日期（含時間）。
#
|
方法
|
ORACLE
|
MSSQL
|
1
|
d1 - d2
|
差距天數（數值型態-浮點數）
時分秒: 1.運算(乘24、60、60)
2.轉換為INTERVAL型態
|
差距時間-相對基準日期（日期型態）
差距天數：1. 轉為數值型態
2. 改用DATEDIFF函數。
|
2
|
函數
|
MONTHS_BETWEEN(Edate, SDate)
|
DATEDIFF(Interval , SDate, EDate)
|
傳回以月為單位的差距
（可為小數）
|
傳回兩個日期間相差的時間間隔單位數目（帶正負號的整數）
|
MSSQL
MSSQL提供DATEDIFF 函數，傳回兩個日期間相差的時間間隔單位（如年、季、月、日、時、分及秒）之數目(整數)，語法如下:
語法
|
說明
|
DATEDIFF(Interval , SDate, EDate)
|
- 1st: 時間間隔單位（Interval）: 年月日時分秒
- 2nd、3rd: 目標日期
|
第一個引數為時間間隔單位，用以指定時間差異運算的時間間隔單位，參數如下表。
第二個引數、第三個引數為目標日期。
傳回值為兩時間的單位差異個數，此函數傳回值的資料型態為整數。
Interval
|
縮寫
|
年
|
yy、yyyy
|
季
|
qq、q
|
月
|
mm、m
|
日
|
dd、d
|
週
|
wk、ww
|
時
|
hh
|
分
|
mi、n
|
秒
|
ss、s
|
毫秒
|
ms
|
SQL
|
SELECT DT
, DATEDIFF(DD, '2020-01-01
22:45', DT) Diff
, CAST(CAST(DT as datetime) as float) "DT(Float)"
, FLOOR(CAST(CAST(DT as datetime) as float)) "DT(Int)"
, CAST(
CAST(DT as datetime)
- CAST('2020-01-01
22:45' AS datetime)
as float) Ref
--, CAST(DT as
datetime)
-
CAST('2020-01-01 22:40' AS datetime)
Ref1
FROM (VALUES ('2020-01-01 00:00')
, ('2020-01-01
22:45')
, ('2020-01-02
00:00')
) T (DT)
註: 2020-01-01 22:45為比較基準點(Now)
|
||||||||||||||||||||||||
結果
|
0.0520833333333333
* 24 = 1.2499999999999992 (1小時)
0.2499999999999992
* 60 = 14.9999999999999520 (約15分鐘)
註: 本案例為呈現概念，因此將時間差轉為浮點數後，再以運算方式轉回時間產生誤差。
|
DATEADD (Interval
, count, date)
|
第二個引數為增、減時間間隔單位的次數（Count），此值可為正數（未來），或負數（過去），但需特別注意，此值可接受浮點數，當指定的數值不是整數時，系統會自動捨棄值小數部分，即可說明MSSQL日期運算概念。上表中以FLOOR函數進行捨位運算後，可得知:
(#2 / #1)與基準日期(1900-01-01)差距均為43829，兩者相減後為『0』，兩者時差為22:45；
(#2 / #3)則分別為43829及43830則兩差距為1單位(天)，兩者時差為01:15。
案例中，兩者時間差雖1.25小時但已跨日，DATEDIFF函數依定義傳回為1天，運算時先進行捨位計算再進行差異比較，此即為MSSQL日期運算概念，可能不符合一些需求原始想法，常見會將間隔單位降為『秒』再行運算比較。
ORACLE
ORALCE提供MONTHS_BETWEEN函數，此函數是以月為計算單位的時間差異函數，由於實際案例中鮮少使用(未曾使用)而不多作著墨，如需使用可自行參考官方文件。
本文主要探討兩個日期相減(d1 - d2)將可得到數值型態的差異天數（浮點數），再將「差異天數」以運算方式轉換成其他單位，或轉換為INTERVAL型態後再行運用，如下。
SQL
|
SELECT TO_CHAR(NOW, 'YYYY-MM-DD
HH24:MI:SS')
"Now"
--, TO_CHAR(Target, 'YYYY-MM-DD')
"Target"
,
Target - Now
"Diff"
, (Target
- Now) DAY(8) TO SECOND INTVL
,
REGEXP_REPLACE(
TO_CHAR((Target
- Now) DAY(8) TO SECOND)
, '(^.{1})(0*)(.+)(\.{1}.*$)'
, '\1\3'
) "INTVL(char)"
FROM
(
SELECT
TO_DATE('202001012245', 'YYYYMMDDHH24MISS')
Now
, DATE'2020-01-02'
Target
FROM
DUAL
)
註: 2020-01-01 22:45為比較基準點(Now)
|
||||||||
結果
|
0.0520833333333333
* 24 = 1.2499999999999992 (1小時)
0.2499999999999992
* 60 = 14.9999999999999520 (約15分鐘)
|
沒有留言:
張貼留言