Oracle 11g如何查询非日期格式的VARCHAR类型日期列值

嗨，我来帮你搞定这个需求！Oracle 11g确实没有像SQL Server那样内置的ISDATE()函数，但我们有两种实用的方法来找出insertDate和ModifiedDate这两个VARCHAR列里的非日期格式值。

方法一：使用正则表达式快速匹配格式 #

如果你的日期有固定格式（比如YYYY-MM-DD、DD/MM/YYYY这类），可以用REGEXP_LIKE来筛选不符合格式的记录。这种方法速度快，但只能验证格式，没法判断日期是否合法（比如2月30号这种格式对但实际不存在的日期）。

举个例子，假设你的日期格式是YYYY-MM-DD，查询语句如下：

SELECT insertDate, ModifiedDate
FROM mytable
WHERE NOT REGEXP_LIKE(insertDate, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')
   OR NOT REGEXP_LIKE(ModifiedDate, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$');

如果是DD/MM/YYYY格式，把正则改成^[0-9]{2}/[0-9]{2}/[0-9]{4}$就行。

方法二：自定义IS_DATE函数（准确验证日期合法性） #

如果需要严格验证字符串能否转换成有效的日期（包括排除非法日期），最好自定义一个类似ISDATE的函数，利用Oracle的异常处理来判断。

第一步：创建自定义函数 #

CREATE OR REPLACE FUNCTION IS_DATE(p_str VARCHAR2) RETURN VARCHAR2 IS
    v_date DATE;
BEGIN
    -- 这里替换成你的实际日期格式掩码，比如'YYYY-MM-DD'或'DD/MM/YYYY'
    v_date := TO_DATE(p_str, 'YYYY-MM-DD');
    RETURN 'YES';
EXCEPTION
    WHEN OTHERS THEN
        RETURN 'NO';
END;
/

如果你的日期有多种格式，可以扩展函数，尝试多种格式转换：

CREATE OR REPLACE FUNCTION IS_DATE(p_str VARCHAR2) RETURN VARCHAR2 IS
    v_date DATE;
BEGIN
    -- 尝试第一种格式
    v_date := TO_DATE(p_str, 'YYYY-MM-DD');
    RETURN 'YES';
EXCEPTION
    WHEN OTHERS THEN
        BEGIN
            -- 尝试第二种格式
            v_date := TO_DATE(p_str, 'DD/MM/YYYY');
            RETURN 'YES';
        EXCEPTION
            WHEN OTHERS THEN
                RETURN 'NO';
        END;
END;
/

第二步：使用自定义函数查询 #

现在你就可以用类似你示例的语句来查询了：

SELECT insertDate, ModifiedDate
FROM mytable
WHERE IS_DATE(insertDate) = 'NO'
   OR IS_DATE(ModifiedDate) = 'NO';

注意：自定义函数在处理大表时性能会比正则稍差，但胜在准确性更高，能真正识别出无法转换成有效日期的字符串。

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