MSSQL中NVARCHAR转指定日期格式及插入DATETIME列报错解决

嘿，这个问题我之前帮同事排查过，咱们先理清楚问题根源，再一步步解决：

为什么会报错？ #

你遇到的错误：

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

本质原因是MSSQL默认的日期解析规则不识别你输入的DD-MM-YYYY格式。默认情况下，多数会话设置会把18-05-2018当成MM-DD-YYYY，但18作为月份显然超出了1-12的范围，所以触发了转换错误。

怎么正确插入这个日期时间？ #

你需要用显式转换指定字符串的格式，告诉MSSQL这是日-月-年的结构。MSSQL的CONVERT函数支持指定样式代码，这里用样式105（对应意大利日期格式DD-MM-YYYY）就可以完美匹配你的字符串：

INSERT INTO YourTable (YourDateTimeColumn)
VALUES (CONVERT(DATETIME, '18-05-2018 11:09:31 AM', 105))

如果担心输入的字符串可能有格式错误，可以用TRY_CONVERT替代，转换失败时会返回NULL而不是直接报错：

INSERT INTO YourTable (YourDateTimeColumn)
VALUES (TRY_CONVERT(DATETIME, '18-05-2018 11:09:31 AM', 105))

关于「保留格式」的重要说明 #

这里要纠正一个常见误解：MSSQL的DATETIME类型本身并不存储字符串格式，它是用二进制形式存储日期和时间的数值。你看到的“格式”其实是客户端工具（比如SSMS）根据你的系统/会话设置显示出来的。

如果想要查询时得到18-05-2018 11:09:31 AM这样的格式，需要在查询时把DATETIME值转换为字符串：

方法1：用FORMAT函数（直观易读）

SELECT FORMAT(YourDateTimeColumn, 'dd-MM-yyyy hh:mm:ss tt') AS FormattedDateTime
FROM YourTable

tt会自动显示AM/PM，hh是12小时制的小时数。

方法2：用CONVERT函数（性能更优）

如果对性能有要求，优先用CONVERT组合日期和时间部分：

SELECT 
  CONVERT(VARCHAR(10), YourDateTimeColumn, 105) + ' ' + 
  CONVERT(VARCHAR(10), YourDateTimeColumn, 109) AS FormattedDateTime
FROM YourTable

总结 #

1. 插入时用CONVERT(DATETIME, 你的字符串, 105)解决转换报错；
2. 存储的是日期时间数值，格式是显示层面的需求，查询时再转换；
3. 不确定输入合法性时，用TRY_CONVERT提高代码健壮性。

内容的提问来源于stack exchange，提问作者Nikson