SQL中如何将含英美两种日期格式的varchar列转换为date列

结论先行：不存在能直接、无歧义兼容两种格式的自动转换方式，必须先做格式区分再完成转换，强行自动转换必然出现日期解析错误。

核心矛盾是两种格式存在完全无法自动判定的重叠区间：

• 当日期字符串的斜杠分隔的前两位数值都落在1~12范围内时，没有任何通用规则能判断它到底是英式的「日/月/年」还是美式的「月/日/年」。比如字符串03/04/2022，按英式解析是4月3日，按美式解析是3月4日，没有额外信息的话这类值的解析准确率根本无法保证。

可落地的正确处理步骤 #

• 第一步：新增格式标记字段
  给原表加一个tinyint类型的标记列，比如命名为date_standard，约定值为1代表英式日/月/年，值为2代表美式月/日/年。
• 第二步：自动标记无歧义数据
  先把不存在判定冲突的数据自动补全标记：
  • 如果字符串按斜杠拆分后的第一段数值>12，那一定是英式格式——因为美式格式第一段是月份，取值不可能超过12
  • 如果字符串按斜杠拆分后的第二段数值>12，那一定是美式格式——因为英式格式第二段是月份，取值不可能超过12
• 第三步：人工核对歧义数据
  剩下的拆分后前两位数值都≤12的记录，必须通过业务溯源、录入端日志核对、数据来源地区匹配等方式确认格式，补全标记，这一步没有捷径可走。
• 第四步：按标记解析生成date列
  新增date类型的目标列，根据每行的格式标记调用对应解析规则写入即可，以MySQL为例的参考代码：

-- 新增存储解析结果的日期列
ALTER TABLE your_table ADD COLUMN actual_date DATE;

-- 按标记分格式解析更新
UPDATE your_table
SET actual_date = CASE
    WHEN date_standard = 1 THEN STR_TO_DATE(old_varchar_date_col, '%d/%m/%Y')
    WHEN date_standard = 2 THEN STR_TO_DATE(old_varchar_date_col, '%m/%d/%Y')
END;

注意：不要使用任何声称可以自动识别两种格式的转换函数/工具，这类工具本质上是对歧义数据做了随机/规则猜测，解析错误率会直接影响后续所有日期相关的统计、关联计算结果，造成业务数据错误。

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