hit counter for blogger

黑暗執行緒

 黑暗執行緒搬新家囉!! http://www.darkthread.net

1/03/2007

KB-Oracle轉SQL的日期陷阱

在SQL 2005上建了Linked Server連到ORALCE,轉檔的SQLCLR Procedure卻一直發生疑似日期轉換的錯誤:
Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

過去這類的錯誤都是因為SQL中不允許NULL的日期欄位,在Oracle中有設了NULL值所引起的,但反覆清查多次,排除光所有為NULL的日期欄位,錯誤依舊。忽然福至心靈,會不會是日期範圍不同所致?

SQL的DateTime型別: 1753/01/01 - 9999/12/31
ORACLE的Date型別: "西元前"4712/01/01 - 9999/12/31

結果用WHERE ArriveDate < TO_DATE('1900/01/01','YYYY/MM/DD')一查,測試資料庫中有名員工的到職日竟是西元998年9月8日,就是這位來自宋朝的老兄,讓SQL轉換日期出了錯。(嘖嘖嘖,以他的年資,退休金應該破億吧!!)

下回再遇到ORACLE與SQL間日期轉換有問題時,記得查一下日期範圍。

【後記】
後來還發生了怪異的事,將ORACLE有問題的資料刪除後,SELECT該Table(假設為Emp)仍有同樣的問題。用CREATE TABLE EmpClone AS SELECT * FROM Emp的方法複製了另一個相同的Table(EmpClone),卻可以正常SELECT;即使將Emp的資料全部清空、SQL 2005 Restart,SQL 2005還是抱怨無法轉日期。
最後,我將Emp TABLE DROP後重建,再將資料倒回來,居然就OK了。之後另建了一個Table,故意塞入999/01/01,SELECT時會出現日期轉換錯誤,但把資料刪除後就正常,看來先前的情況只好歸入X檔案了。

2 Comments:

  • 不管是在ORACLE還是SQL
    都被日期欄位扁過
    到底日期欄位有什麼好處?
    用文字的方式存yyyymmdd
    沒有比較好嗎?

    By Anonymous steve, at 1/04/2007 11:42 pm  

  • 我想到用日期欄位的好處:
    1) DB端提供了資料的驗證,用VARCHAR,難保不會存入200A0231這種火星日期
    2) 可以直接在SQL語法中做加一天、加一個月的運算
    3) 與.NET等程式整合時,可以直接轉成DateTime Object
    被扁時,就想想這些好處吧!!

    By Blogger Darkthread, at 1/05/2007 11:03 am  

Post a Comment

<< Home