检查在varchar2中声明的有效日期

Question

我的表如下所示，该表在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

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

Answer 1

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

首先， 永远不要 将 DATE 存储为 VARCHAR2 。所有这些开销是由于您的 设计存在缺陷 。

‘20100231’

究竟怎么算是有效日期？ 哪个日历在2月有31天？

跟着这些步骤：

1. 添加一个具有DATE DATA TYPE的新列。
2. 使用 TO_DATE 使用旧列中的日期值更新新列。
3. 在新的DATE列上执行所需的DATE算术，或在第2步本身的UPDATE语句中进行处理。
4. 删除旧列。
5. 将新列重命名为旧列。

更新 添加演示

设置

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>