SQL中含NULL值的varchar日期列转date类型报错解决问询

嘿，这个问题我之前踩过坑！你遇到的Conversion failed when converting date and/or time from character string报错，其实跟NULL没关系——NULL本身是可以正常转成DATE类型的，问题出在你的列里藏着不符合DATE格式的脏数据，或者是数据库没法自动识别的日期字符串（比如你例子里的1/30/2010是MM/DD/YYYY格式，有些数据库默认的日期格式不匹配）。下面给你一套靠谱的解决流程：

第一步：先找出所有“脏数据” #

首先得定位那些非NULL但无法转换为DATE的字符串，比如拼写错误的日期（像13/30/2010）、格式混乱的内容（比如2010-30-01）或者干脆不是日期的文本（比如abc）。

如果你用的是SQL Server： #

SELECT Date_col
FROM df
WHERE TRY_CONVERT(date, Date_col) IS NULL
AND Date_col IS NOT NULL;

如果你用的是MySQL： #

SELECT Date_col
FROM df
WHERE STR_TO_DATE(Date_col, '%m/%d/%Y') IS NULL
AND Date_col IS NOT NULL;

拿到这些脏数据后，要么把它们修正成正确的日期格式，要么标记/删除掉——这是解决错误的核心前提。

第二步：安全转换日期列 #

直接用ALTER TABLE df ALTER COLUMN Date_col date很容易因为格式不匹配或者遗漏的脏数据失败，建议用临时列过渡的方法，既安全又能验证转换结果：

SQL Server版本： #

1. 先添加一个临时DATE类型的列：

ALTER TABLE df ADD Temp_Date date;

2. 把原列的日期转换后插入临时列（指定MM/DD/YYYY格式，样式码101）：

UPDATE df
SET Temp_Date = CONVERT(date, Date_col, 101)
WHERE Date_col IS NOT NULL;

3. 验证Temp_Date里的数据没问题后，删除原列并重命名临时列：

ALTER TABLE df DROP COLUMN Date_col;
EXEC sp_rename 'df.Temp_Date', 'Date_col', 'COLUMN';

MySQL版本： #

1. 添加临时列：

ALTER TABLE df ADD Temp_Date date;

2. 转换并插入数据（指定%m/%d/%Y对应MM/DD/YYYY格式）：

UPDATE df
SET Temp_Date = STR_TO_DATE(Date_col, '%m/%d/%Y')
WHERE Date_col IS NOT NULL;

3. 删除原列并修改临时列名称：

ALTER TABLE df DROP COLUMN Date_col;
ALTER TABLE df CHANGE Temp_Date Date_col date;

最后提醒 #

• NULL值本身不会触发这个错误，别把锅甩给它😉
• 提前排查脏数据能避免后续很多麻烦
• 用临时列过渡的方式比直接ALTER COLUMN更稳妥，能在修改原列前确认所有转换都正确

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