如何查询非空且不符合yyyy-mm-dd格式的textDate列对应行的ID

主流数据库实现方式 #

MySQL/MariaDB #

• 仅验证格式：用正则表达式反向匹配不符合yyyy-mm-dd格式的记录

SELECT id
FROM your_table_name
WHERE textDate IS NOT NULL
  AND textDate NOT REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$';

• 验证格式+日期合法性：结合STR_TO_DATE函数，转换失败则说明内容无效（包括格式错误和日期本身不合法，比如2023-02-30）

SELECT id
FROM your_table_name
WHERE textDate IS NOT NULL
  AND STR_TO_DATE(textDate, '%Y-%m-%d') IS NULL;

PostgreSQL #

• 正则筛选格式不符记录

SELECT id
FROM your_table_name
WHERE textDate IS NOT NULL
  AND textDate !~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$';

• 验证格式+合法性：尝试将字符串转换为DATE类型，转换失败即为无效内容

SELECT id
FROM your_table_name
WHERE textDate IS NOT NULL
  AND textDate::DATE IS NULL;

SQL Server #

使用TRY_CONVERT函数，指定yyyy-mm-dd对应的样式代码23，转换失败则说明内容无效

SELECT id
FROM your_table_name
WHERE textDate IS NOT NULL
  AND TRY_CONVERT(DATE, textDate, 23) IS NULL;

两种验证方式对比 #

• 正则方式：执行速度快，适合快速排除格式明显错误的场景，但无法识别格式正确但实际无效的日期。
• 日期转换方式：准确性更高，同时校验格式和日期合法性，但数据量极大时性能略低于正则。

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