在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檔案了。


Comments

# by steve

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

# by Darkthread

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

# by bongbonggun

來自宋朝的老兄XDDDD 日期真的很難搞呀!!!!

Post a comment


7 + 1 =