如何在SQL Server中将dd-MM-yyyy hh:mm:ss格式字符串转为datetime

之前尝试失败的原因 #

你的转换语句存在两个核心问题：

• CONVERT函数第一个参数为目标数据类型，你写的是nvarchar(MAX)，等价于将字符串转为字符串，不会完成datetime类型转换
• 格式码121对应的是yyyy-MM-dd HH:mm:ss.fff的格式，和你存储的dd-MM-yyyy hh:mm:ss格式不匹配，因此转换失败

单值转换示例 #

针对你给出的样例26-11-2021 08:19:16，可以使用格式码105完成转换，格式码105对应dd-MM-yyyy规则，SQL Server会自动兼容后续的时分秒部分：

SELECT CONVERT(datetime, '26-11-2021 08:19:16', 105) AS 转换后datetime值

如果是SQL Server 2012及以上版本，也可以用指定区域的解析方法避免格式兼容问题：

SELECT TRY_PARSE('26-11-2021 08:19:16' AS datetime USING 'en-GB') AS 转换后datetime值

整表转换前的脏数据排查 #

正式修改表结构前，建议先排查无法转换的脏数据，避免批量更新失败：

-- 查询所有无法正常转换的行，提前处理异常数据
SELECT * 
FROM 你的表名
WHERE TRY_CONVERT(datetime, 你的字符串列名, 105) IS NULL
AND 你的字符串列名 IS NOT NULL;

安全修改列类型为datetime的步骤 #

建议通过新增列的方式过渡，避免原数据丢失：

1. 新增临时datetime类型列

ALTER TABLE 你的表名 ADD 临时日期列 datetime;

2. 批量完成数据转换赋值

UPDATE 你的表名
SET 临时日期列 = CONVERT(datetime, 原字符串列名, 105);

3. 验证临时列数据完全正确后，替换原列（如果需要保留原列名）

ALTER TABLE 你的表名 DROP COLUMN 原字符串列名;
EXEC sp_rename '你的表名.临时日期列', '原字符串列名', 'COLUMN';

关于「保留原有时间格式」的说明 #

datetime类型本身为二进制存储，不存在「存储格式」的概念，你之前看到的dd-MM-yyyy hh:mm:ss是字符串的展示格式。如果后续查询需要输出该格式，可以在查询时转换为字符串即可：

SELECT CONVERT(varchar(19), 你的datetime列名, 105) + ' ' + CONVERT(varchar(8), 你的datetime列名, 108) AS 格式化日期
FROM 你的表名

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