检查 varchar2 中声明的有效日期

我的表如下所示，在 VARCHAR2 中声明：

YMD  
20101010  
20101112  
20100231  
20150101  
20160101  

我必须检查有效日期并从 sysdate 中过滤未来日期，这些日期的格式有效。

我编写如下函数来检查有效日期：

create or replace FUNCTION VALIDATE_DATE (p_string in string) return date is  
begin  
    return to_date(p_string, 'YYYYMMDD');  
exception when others then  
    begin  
        return to_date(p_string, 'YYYY-MM-DD');  
    exception when others then  
        begin  
            return to_date(p_string, 'RR-MON-DD');  
        exception when others then  
            return null;  
        end;  
    end;  
end;  

并编写此查询来检查有效日期并用 null 替换无效日期

select ymd, VALIDATE_DATE(ymd) as Valid 
from temp

为了检查未来的日期，我编写了以下查询，但它抛出错误

ORA-01839

select ymd 
from temp
where validate_date(ymd)='YES'
and to_date(ymd,'yyyymmdd')>sysdate

如何检查表中的未来日期（如果存在）？

我宁愿修复设计问题作为永久解决方案，而不是在解决方法上浪费时间。

首先，NEVER store DATE as VARCHAR2。所有这些开销都是由于您的设计有缺陷.

'20100231'

这到底怎么可能是一个有效的日期呢？哪个日历的 2 月有 31 天？

按着这些次序：

1. 添加具有日期数据类型的新列。
2. 使用旧列中的日期值更新新列TO_DATE.
3. 对新的 DATE 列执行所需的 DATE 算术，或在步骤 2 本身的 UPDATE 语句中处理此问题。
4. 删除旧的列。
5. 将新列重命名为旧列。

UPDATE添加演示

Setup

SQL> CREATE TABLE t
  2      (ymd varchar2(8));

Table created.

SQL>
SQL> INSERT ALL
  2      INTO t (ymd)
  3           VALUES ('20101112')
  4      --INTO t (ymd)
  5      --     VALUES ('20100231')
  6      INTO t (ymd)
  7           VALUES ('20150101')
  8      INTO t (ymd)
  9           VALUES ('20160101')
 10  SELECT * FROM dual;

3 rows created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>

添加新列：

SQL> ALTER TABLE t ADD (dt DATE);

Table altered.

SQL>

执行所需的更新

SQL> UPDATE t
  2  SET dt =
  3    CASE
  4      WHEN to_date(ymd, 'YYYYMMDD') > SYSDATE
  5      THEN NULL
  6      ELSE to_date(ymd, 'YYYYMMDD')
  7    END;

3 rows updated.

SQL>
SQL> COMMIT;

Commit complete.

SQL>

让我们检查：

SQL> SELECT * FROM t;

YMD      DT
-------- ---------
20101112 12-NOV-10
20150101 01-JAN-15
20160101

SQL>

删除旧列：

SQL> ALTER TABLE t DROP COLUMN ymd;

Table altered.

SQL>

将新列重命名为旧列名称

SQL> ALTER TABLE t RENAME COLUMN dt TO ymd;

Table altered.

SQL>

您刚刚解决了问题

SQL> SELECT * FROM t;

YMD
---------
12-NOV-10
01-JAN-15


SQL>