SQL Server 字串轉時間 資料驗證

最近要從一個資料庫將資料轉移到另外一個資料庫，期間涉及到一些字串轉時間類型出現了很多問題，將自己的一點愚見貼上來備忘。

CASE WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.][1-9][-.][1-9]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.]0[1-9]-0[1-9]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.]0[1-9]-[1-9]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.][1-9]-0[1-9]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.][13456789][-.][12][0-9]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.]0[13456789][-.][12][0-9]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.][13578][-.]3[01]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.]0[13578][-.]3[01]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.][469][-.]30' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.]0[469][-.]30' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.]2[-.]1[0-9]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.]02[-.]1[0-9]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.]2[-.]2[0-8]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.]02[-.]2[0-8]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.]1[012][-.][1-9]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.]1[012][-.]0[1-9]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.]1[012][-.][12][0-9]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.]1[02][-.]3[01]' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)WHEN cnct_char31 LIKE '[12][0-9][0-9][0-9][-.]11[-.]30' THEN CAST(CONVERT(datetime,cnct_char31,120) AS VARCHAR)ELSE NULL END

若有疑問或不正之處，歡迎提出指正和討論。