時間字串的資料如下:
2008/10/21 下午 06:01:39
現在要透過c#將資料寫回oracle內,若直接使用
TO_DATE('2008/10/21 下午 06:01:39', 'yyyy-mm-dd hh:mi:ss')
會造成 ORA-01858: a non-numeric character was found where a numeric was expected
解法:
1.先用將原始字串轉換
Convert.ToDateTime("2008/10/21 下午 06:01:39").ToString("u")
u是 Universal sortable (invariant) 格式,輸出結果像是 2006-04-17 21:22:48Z
(ms-help://MS.VSCC.v90/MS.MSDNQTR.v90.cht/fxref_mscorlib/html/35b48850-3558-6011-1368-facb5f7986bd.htm#seeAlsoToggle)
字尾有多個Z,用Substring砍掉
Convert.ToDateTime(payDate).ToString("u").Substring(0, 19)
2. 使用TO_DATE寫回Date欄位
TO_DATE('<將轉換過的字串代入>', 'yyyy-mm-dd hh24:mi:ss')
Sample:
string payTool = (node.SelectSingleNode(".//PayTool")).InnerText;
string paySerial = (node.SelectSingleNode(".//PaySerial")).InnerText;
string payResult = (node.SelectSingleNode(".//PayResult")).InnerText;
string payDate = (node.SelectSingleNode(".//AuthorizeDateTime")).InnerText;
string sql = string.Format("UPDATE ORDERS SET HOWTOPAY = '{0}', PAYRESULT = '{1}', PAYDATE = TO_DATE('{2}','yyyy-mm-dd hh24:mi:ss'), STATUS = '{3}' WHERE ID = '{4}'", payTool, payResult, Convert.ToDateTime(payDate).ToString("u").Substring(0, 19), "1", paySerial);
留言列表