SQL DateTime2 CONVERT 時間字串地雷
2 | 3,391 |
同事分享的茶包 - 踩到 SQL DateTime2 CONVERT 轉換的地雷。
SQL 的 CONVERT(VARCHAR(..), DateTime_Type, Style_Number) 可將日期型別轉成字串,格式由 Style_Number 決定,例如:110 是 yyyy-MM-dd、127 是 yyyy-mm-ddThh:mi:ss.mmmZ (說明文件),某系統使用了 114 將日期欄位轉成時間字串,卻發生同一個時間,存成 DateTime 或 DateTime2 CONVERT 轉換結果不同的狀況。
講到 DateTime 與 DateTime2,讀者們或許聯想到是 DateTime 毫秒只有 0,3,7 三種尾數搞的鬼,但並不是,就算純日期也會爆炸,請看示範:
追究原因,DateTime 與 DateTime2 的Convert(VARCHAR(12), colName, 114) 的轉換結果不同,DateTime 是 HH:mm:ss:fff,而 DateTimre2 則是 HH:mm:ss.fff,秒與毫秒的分隔號不同,一個是冒號,一個是句號。依據官方文件,114 的輸出格式應為「hh:mi:ss:mmm (24h)」,DateTime 輸出冒號才是對的,我找不到 DateTime2 轉換規則不同的文獻,而同為時間卻刻意與 DateTime 不一致並不合情理(根本是在挖坑呀),故我推測這是 SQL Server 實作 DateTime2 CONVERT 時粗心留下的 Bug,可能踩到的人不多,又或者修正它會讓某些將錯就錯的現有程式爆炸,所以才遺留至今吧?我猜大家遇到的機會應該也不大,留個印象就好。
A case of different result of style 114 CONVERT of DateTime and DateTime2.
Comments
# by 小賤健
這個錯誤真是太奇特了。特地做了實驗才能發現。 實際應用上幸好還沒踩到這個雷。慬記!
# by 路人
這不是錯誤,Datetime非ISO 8601標準格式,而Datetime2為ISO 8601格式(https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms190977(v=sql.90)),所以毫秒以下就是(.) Convert 112的Standard為 - 非任何標準,所以顯示為該格式的狀態表示 (https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms187928(v=sql.90)?redirectedfrom=MSDN#arguments) 因此要對比datetime與datetime2格式,可使用126,"SELECT convert(varchar(23), dt2, 126) from d"