将字符串转为时间类型时出现无效时间字符串错误的解决方法

一、删除包含无效时间字符串的行 #

如果确定不需要保留无效数据，可以通过数据库自带的时间转换验证函数筛选并删除无效行，不同SQL方言的实现如下：

MySQL/MariaDB #

使用STR_TO_DATE函数，无法转换为指定格式的字符串会返回NULL，据此删除无效行：

-- 先验证无效行（务必先执行这步确认）
SELECT * FROM your_table WHERE STR_TO_DATE(your_time_column, '%Y-%m-%d %H:%i:%s') IS NULL;

-- 确认后执行删除
DELETE FROM your_table WHERE STR_TO_DATE(your_time_column, '%Y-%m-%d %H:%i:%s') IS NULL;

注意：替换%Y-%m-%d %H:%i:%s为你的时间列实际格式，比如MM/DD/YYYY对应%m/%d/%Y。

SQL Server #

使用TRY_CONVERT函数，转换失败会返回NULL：

-- 先验证无效行
SELECT * FROM your_table WHERE TRY_CONVERT(DATETIME, your_time_column) IS NULL;

-- 执行删除
DELETE FROM your_table WHERE TRY_CONVERT(DATETIME, your_time_column) IS NULL;

PostgreSQL（12+） #

使用TRY_CAST函数直接判断转换有效性：

-- 先验证无效行
SELECT * FROM your_table WHERE TRY_CAST(your_time_column AS TIMESTAMP) IS NULL;

-- 执行删除
DELETE FROM your_table WHERE TRY_CAST(your_time_column AS TIMESTAMP) IS NULL;

二、更优方案：保留数据，转换时处理异常 #

如果不想删除行（避免丢失潜在可修复的数据），可以将无效值转为NULL或指定默认值，再完成列类型转换：

MySQL/MariaDB #

-- 添加新的时间列
ALTER TABLE your_table ADD COLUMN converted_time DATETIME;

-- 将有效字符串转换为时间，无效值设为NULL
UPDATE your_table SET converted_time = STR_TO_DATE(your_time_column, '%Y-%m-%d %H:%i:%s');

-- 验证后可删除原列，并重命名新列
ALTER TABLE your_table DROP COLUMN your_time_column;
ALTER TABLE your_table RENAME COLUMN converted_time TO your_time_column;

SQL Server #

直接利用TRY_CONVERT更新原列，转换失败的会自动设为NULL：

-- 先允许列值为NULL（如果原列是NOT NULL需先调整）
ALTER TABLE your_table ALTER COLUMN your_time_column DATETIME NULL;

-- 执行转换
UPDATE your_table SET your_time_column = TRY_CONVERT(DATETIME, your_time_column);

PostgreSQL（12+） #

通过USING子句直接转换列类型，无效值转为NULL：

ALTER TABLE your_table
ALTER COLUMN your_time_column TYPE TIMESTAMP
USING TRY_CAST(your_time_column AS TIMESTAMP);

新手注意事项 #

• 操作前必须备份数据，避免误操作导致数据丢失
• 始终先通过SELECT语句验证筛选/转换逻辑，确认结果符合预期后再执行修改/删除
• 时间格式字符串必须与原数据完全匹配，格式不匹配会被判定为无效值，比如2023/10/01和2023-10-01需要对应不同的格式符

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