如何在SQL中将字符串格式日期转为datetime并筛选大于指定时间的数据

你之前的查询出错的核心原因有两个：

• 直接用字符串比较时，MM/DD/YYYY格式的字符串字典序和实际时间序不一致，比如08/01/2020按字符串比较会比07/01/2021大，和实际时间顺序完全相反，自然返回错误结果
• 直接用cast转时间戳时，依赖数据库默认的日期解析格式，如果默认格式和你存储的月/日/年 12小时制 上下午格式不匹配，会出现转换错误、转换结果不符合预期的情况

不同数据库的正确写法 #

核心逻辑是显式指定存储格式将字符串转为时间类型后再做比较，不要依赖数据库隐式转换逻辑：

MySQL / MariaDB 写法 #

使用STR_TO_DATE指定格式转换：

SELECT * FROM datatable
WHERE STR_TO_DATE(coldate, '%m/%d/%Y %h:%i:%s %p') > STR_TO_DATE('07/01/2021 01:03:47 AM', '%m/%d/%Y %h:%i:%s %p')

格式符说明：%m两位月份、%d两位日期、%Y四位年份、%h12小时制小时、%i分钟、%s秒、%pAM/PM标识。

PostgreSQL 写法 #

使用TO_TIMESTAMP指定格式转换：

SELECT * FROM datatable
WHERE TO_TIMESTAMP(coldate, 'MM/DD/YYYY HH12:MI:SS AM') > TO_TIMESTAMP('07/01/2021 01:03:47 AM', 'MM/DD/YYYY HH12:MI:SS AM')

SQL Server 写法 #

使用TRY_CONVERT指定样式码转换（可避免非法日期值导致查询报错）：

SELECT * FROM datatable
WHERE TRY_CONVERT(DATETIME, coldate, 22) > TRY_CONVERT(DATETIME, '07/01/2021 01:03:47 AM', 22)

Oracle 写法 #

使用TO_DATE指定格式和语言环境，避免AM/PM识别失败：

SELECT * FROM datatable
WHERE TO_DATE(coldate, 'MM/DD/YYYY HH:MI:SS AM', 'NLS_DATE_LANGUAGE = AMERICAN') > TO_DATE('07/01/2021 01:03:47 AM', 'MM/DD/YYYY HH:MI:SS AM', 'NLS_DATE_LANGUAGE = AMERICAN')

优化建议 #

• 长期使用的话建议将该字段类型修改为DATETIME/TIMESTAMP类型，既可以避免转换开销，也可以直接使用索引提升查询性能
• 若无法修改表结构，可以针对转换函数创建函数索引，比如MySQL可以创建INDEX idx_coldate (STR_TO_DATE(coldate, '%m/%d/%Y %h:%i:%s %p'))，避免全表扫描

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