检查在varchar2中声明的有效日期 [英] check for valid date which is declared in varchar2

问题描述

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

My table looks like below which is declared in VARCHAR2:

YMD  
20101010  
20101112  
20100231  
20150101  
20160101  

我必须检查有效日期并从sysdate过滤未来的日期，这些日期应采用有效格式.

I have to check for valid dates and filter future dates from sysdate, which are in valid format.

我编写以下函数来检查有效日期:

I write the function as below to check for valid dates:

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代替无效日期

and written this query to check for valid dates and replace with null for invalid dates

select ymd, VALIDATE_DATE(ymd) as Valid 
from temp

并检查未来的日期，我写了以下查询，但是会引发错误

and to check future dates I wrote the following query, but it throws error

ORA-01839

ORA-01839

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

如何检查表格中将来的日期(如果存在)?

How to check future dates in my table if exists?

推荐答案

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

I will rather fix the design issue as a permanent fix rather than wasting time on the workaround.

首先，从不将日期存储为 VARCHAR2 .所有这些开销归因于您的设计存在缺陷.

Firstly, NEVER store DATE as VARCHAR2. All this overhead is due to the fact that your design is flawed.

'20100231'

'20100231'

那怎么可能是一个有效的日期?哪个日历在2月有31天?

How on earth could that be a valid date? Which calendar has a 31 days in FEBRUARY?

请按照以下步骤操作:

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

1. Add a new column with DATE DATA TYPE.
2. Update the new column with date values from the old column using TO_DATE.
3. Do the required DATE arithmetic on the new DATE column, or handle this in the UPDATE statement in step 2 itself.
4. Drop the old column.
5. Rename the new column to the old column.

更新添加演示

设置

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>

这篇关于检查在varchar2中声明的有效日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！