SQL中如何将不同格式的字符串类型日期转换后比较大小？

问题原因 #

报错的核心原因有两点：

1. 直接调用CONVERT(datetime, 字符串)会使用数据库默认的日期规则解析内容，和你实际使用的mm/yy、dd/mm/yy格式不匹配，自然会解析失败
2. 字符串类型存储的日期字段大概率存在不符合格式的脏数据，也会触发转换报错

解决方法 #

以下以SQL Server语法为例，其他数据库可参考逻辑调整对应日期转换函数即可：

基础修正方案（无脏数据场景） #

CONVERT函数支持传入第三个参数指定输入的日期格式码，dd/mm/yy格式对应的格式码为3；mm/yy格式没有直接对应的格式码，需要先手动补全为01/mm/yy（默认取当月1号作为日期的日部分）后再用格式码3转换：

SELECT * 
FROM 你的实际表名
WHERE 
  -- 补全日部分后转换Column1
  CONVERT(datetime, '01/' + Column1, 3) > 
  -- 直接转换Column2
  CONVERT(datetime, Column2, 3)

兼容脏数据方案 #

如果字段中存在不符合格式的脏数据，可以用TRY_CONVERT（SQL Server 2012及以上版本支持）替代CONVERT，转换失败时会返回NULL而非直接中断查询报错：

SELECT * 
FROM 你的实际表名
WHERE 
  TRY_CONVERT(datetime, '01/' + Column1, 3) > 
  TRY_CONVERT(datetime, Column2, 3)

如果需要排查所有脏数据，可执行以下语句查询转换失败的记录：

SELECT Column1, Column2 
FROM 你的实际表名
WHERE 
  TRY_CONVERT(datetime, '01/' + Column1, 3) IS NULL
  OR TRY_CONVERT(datetime, Column2, 3) IS NULL

其他数据库语法适配 #

• MySQL：使用STR_TO_DATE函数，Column1转换逻辑为STR_TO_DATE(Column1, '%m/%y')，Column2转换逻辑为STR_TO_DATE(Column2, '%d/%m/%y')
• Oracle：使用TO_DATE函数，Column1转换逻辑为TO_DATE(Column1, 'MM/YY')，Column2转换逻辑为TO_DATE(Column2, 'DD/MM/YY')

内容的提问来源于stack exchange，提问作者Михаил