Oracle SQL中验证VARCHAR列日期有效性及闰年并筛选无效行

首先是用于创建测试表并插入数据的SQL：

CREATE TABLE DATE_TEST(DD VARCHAR2(40));

Insert into DATE_TEST VALUES('30-02-24');
Insert into DATE_TEST VALUES('12-01-24');
INSERT INTO DATE_TEST VALUES('15');

需求是筛选出**不符合日期格式或日期逻辑（比如闰年判断）**的无效记录，预期输出包含30-02-24（2月没有30日）和15（格式不匹配）。

解决方案 #

适用于Oracle 12c及以上版本

可以使用VALIDATE_CONVERSION函数直接验证字符串是否能转换为指定格式的日期，写法简洁高效：

SELECT DD
FROM DATE_TEST
WHERE VALIDATE_CONVERSION(DD AS DATE, 'DD-MM-RR') = 0;

逻辑说明

• VALIDATE_CONVERSION(DD AS DATE, 'DD-MM-RR')：检查字段DD能否按照DD-MM-RR的格式转换为日期类型
• 返回值为0表示转换失败（即记录为无效日期），返回1表示转换成功

适用于Oracle 12c之前的版本

可以通过捕获TO_DATE函数的转换异常来判断，比如用子查询方式：

SELECT DD
FROM DATE_TEST
WHERE NOT EXISTS (
    SELECT 1
    FROM DUAL
    WHERE TO_DATE(DD, 'DD-MM-RR') IS NOT NULL
);

也可以自定义判断函数：

CREATE OR REPLACE FUNCTION IS_VALID_DATE(p_date_str VARCHAR2, p_format VARCHAR2) RETURN NUMBER IS
    v_date DATE;
BEGIN
    v_date := TO_DATE(p_date_str, p_format);
    RETURN 1;
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END;
/

SELECT DD
FROM DATE_TEST
WHERE IS_VALID_DATE(DD, 'DD-MM-RR') = 0;

预期输出 #

DD
----------
30-02-24
15

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