PLSQL日期運算之DATE和TIMESTAMP相減算時間差ORA error(ORA-01843, ORA-30076, ORA-01722, ORA-00932)問題

腫魔沒人留言?快點下去搶頭香啊!

參考資料:

  1. TechOnTheNet.com: Oracle / PLSQL: Oracle Error Messages
  2. stack overflow: ORA-30076 when trying to find days between dates
  3. TechOnTheNet.com: Oracle / PLSQL: EXTRACT Function

先依發生時間臚列問題:

  1. ORA-01843: not a valid month
  2. ORA-30076: invalid extract field for extract source
  3. ORA-01722: invalid number
  4. ORA-00932: inconsistent datatypes

起因:計算datatype分別為DATE和TIMESTAMP兩屬性(attribute)之間的時間差,假設叫A和B

原以為將兩者相減就可以得到差幾天,超簡單語法如下:

select sysdate - to_date('20180418', 'yyyymmdd') from dual

也的確跑出意料之中的結果,這時候只要乘以24會得到小時數,再乘以60會得到分鐘數,再乘以60會得到秒數(小學數學的概念),but

代誌不是憨人想的那麼簡單

上面的語法是兩個datetime之間的運算,問題我是要計算datetime和timestamp,天曉得他們計算的結果居然不是天數,而是我的老天鵝啊~無法to_char或to_date或to_number的超固執datetime

老天鵝-有問題嗎

沒辦法,最後只能出大絕招用EXTRACT Function,如:

--假設table A的屬性叫datetime,table B的屬性叫timestamp
select --EXTRACT(year FROM(A.datetime- B.timestamp)) AB_year,  --無此資料,加這段會出錯
       --EXTRACT(month FROM(A.datetime- B.timestamp)) AB_month,--無此資料,加這段會出錯
         EXTRACT(day FROM(A.datetime- B.timestamp)) AB_day, --天數差
         EXTRACT(hour FROM(A.datetime- B.timestamp)) AB_hour, --小時差
         EXTRACT(hour FROM(A.datetime- B.timestamp)) +
         EXTRACT(minute FROM(A.datetime- B.timestamp)) / 60 +
         EXTRACT(second FROM(A.datetime- B.timestamp)) / 3600 TOTAL_hour --先算好總時數差,分鐘和秒換算成時數含在這裡面
from A, B
--(下略)
0 0 vote
Article Rating
訂閱(Subscribe)
來自於(Notify of)
0 Comments
Inline Feedbacks
View all comments