如何筛选text类型列dimension_id中符合日期格式的行？

你的问题出在TO_DATE函数的行为上：当传入的字符串不符合指定日期格式时，它不会返回NULL，而是直接抛出转换错误，导致查询还没执行到IS NOT NULL的判断就中断了，这就是原语句失效的核心原因。

针对不同数据库，有对应的适配方案，同时也可以用正则表达式做前置过滤，提前排除格式明显不符的行：

1. PostgreSQL #

使用TRY_TO_DATE函数，它在转换失败时会返回NULL，完美适配你的需求：

SELECT * FROM table
WHERE TRY_TO_DATE(dimension_id, 'YYYY-MM-DD') IS NOT NULL

如果需要严格验证（比如排除2024-02-31这类无效日期），可以先通过正则过滤格式，再做转换：

SELECT * FROM table
WHERE dimension_id ~ '^\d{4}-\d{2}-\d{2}$'
  AND TRY_TO_DATE(dimension_id, 'YYYY-MM-DD') IS NOT NULL

2. MySQL #

若MySQL未开启严格SQL模式，STR_TO_DATE转换失败会返回NULL，直接使用：

SELECT * FROM table
WHERE STR_TO_DATE(dimension_id, '%Y-%m-%d') IS NOT NULL

如果是严格模式，建议先通过正则过滤，再验证转换：

SELECT * FROM table
WHERE dimension_id REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
  AND STR_TO_DATE(dimension_id, '%Y-%m-%d') IS NOT NULL

3. Oracle（12c及以上版本） #

可以使用TO_DATE的错误处理选项，或者VALIDATE_CONVERSION函数：

方法一：指定转换失败返回NULL #

SELECT * FROM table
WHERE TO_DATE(dimension_id DEFAULT NULL ON CONVERSION ERROR, 'YYYY-MM-DD') IS NOT NULL

方法二：用VALIDATE_CONVERSION验证 #

SELECT * FROM table
WHERE VALIDATE_CONVERSION(dimension_id AS DATE, 'YYYY-MM-DD') = 1

通用前置过滤方案 #

不管使用哪种数据库，都可以先通过正则表达式^\d{4}-\d{2}-\d{2}$过滤掉明显不符合YYYY-MM-DD格式的行（比如你的纯数字字符串），再进行日期转换验证。这个正则能精准匹配年-月-日格式，直接排除无-分隔符的纯数字行，避免转换报错。

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