PostgreSQL中如何检查非DATE类型列的日期格式是否合法？

核心逻辑是尝试将字符串类型的日期值转换为标准DATE类型，转换失败的即为非法格式值。不同数据库有对应的日期校验/转换函数，以下是主流数据库的实现方式：

• MySQL 实现 #

  MySQL 5.7以上版本可以用STR_TO_DATE函数配合IS NULL判断，假设表名为your_table，存储日期的字符串列名为date_str，你可以根据业务要求的合法日期格式调整格式参数，示例如下：

  SELECT date_str AS 非法日期值
  FROM your_table
  WHERE STR_TO_DATE(date_str, '%Y-%m-%d') IS NULL;
  

  如果要兼容多种合法格式，可以叠加OR条件：

  SELECT date_str AS 非法日期值
  FROM your_table
  WHERE STR_TO_DATE(date_str, '%Y-%m-%d') IS NULL
    AND STR_TO_DATE(date_str, '%d/%m/%Y') IS NULL;
  

• Oracle 实现 #

  12c以上版本可以直接使用自带的VALIDATE_CONVERSION函数：

  SELECT date_str AS 非法日期值
  FROM your_table
  WHERE VALIDATE_CONVERSION(date_str AS DATE, 'yyyy-mm-dd') = 0;
  

  低版本可以自定义校验函数后调用，示例函数逻辑：

  CREATE OR REPLACE FUNCTION IS_VALID_DATE(p_str VARCHAR2, p_format VARCHAR2 DEFAULT 'yyyy-mm-dd') RETURN NUMBER IS
    v_date DATE;
  BEGIN
    v_date := TO_DATE(p_str, p_format);
    RETURN 1;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN 0;
  END;
  /
  -- 调用查询
  SELECT date_str AS 非法日期值
  FROM your_table
  WHERE IS_VALID_DATE(date_str, 'yyyy-mm-dd') = 0;
  

• PostgreSQL 实现 #

  12+版本可以直接使用PG_TRY_CAST实现：

  SELECT date_str AS 非法日期值
  FROM your_table
  WHERE PG_TRY_CAST(date_str AS DATE) IS NULL;
  

  低版本自定义校验函数：

  CREATE OR REPLACE FUNCTION IS_VALID_DATE(p_str TEXT) RETURNS BOOLEAN AS $$
  BEGIN
    PERFORM TO_DATE(p_str, 'YYYY-MM-DD');
    RETURN TRUE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN FALSE;
  END;
  $$ LANGUAGE plpgsql;
  -- 调用查询
  SELECT date_str AS 非法日期值
  FROM your_table
  WHERE NOT IS_VALID_DATE(date_str);
  

• SQL Server 实现 #

  直接使用ISDATE函数即可，返回0代表不是合法日期：

  SELECT date_str AS 非法日期值
  FROM your_table
  WHERE ISDATE(date_str) = 0;
  

  如果需要指定特定格式，可以配合TRY_CONVERT：

  SELECT date_str AS 非法日期值
  FROM your_table
  WHERE TRY_CONVERT(DATE, date_str, 23) IS NULL; -- 23对应yyyy-mm-dd格式
  

注意：上述SQL中的格式参数需要根据你业务中定义的合法日期格式调整，比如你的合法格式是MM/DD/YYYY就把对应的格式串换成对应数据库的格式表示即可，就能精准筛选出你举例的12/11、12/2002这类不完整的非法日期。

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