Check a valid date declared in varchar2
My table looks like below which is declared in VARCHAR2:
YMD
20101010
20101112
20100231
20150101
20160101
I need to check for valid dates and filter for future dates from sysdate that are in a valid format.
I am writing a function as shown 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;
and wrote this query to check for valid dates and replace with null for invalid dates
select ymd, VALIDATE_DATE(ymd) as Valid
from temp
and for checking future dates I wrote the following query but it throws an error
OR-01839
select ymd
from temp
where validate_date(ymd)='YES'
and to_date(ymd,'yyyymmdd')>sysdate
How do I check for future dates in my table if they exist?
source to share
I would rather fix the design problem as a permanent fix rather than waste time on a workaround.
First, NEVER store DATE as VARCHAR2 . All this overhead is because your design has flaws .
'20100231'
How could it have been a valid date? Which calendar has 31 days in FEBRUARY?
Follow these steps:
- Add a new column with data type DATE DATA.
- Update the new column with the date values from the old column using TO_DATE .
- Do the required DATE arithmetic on the new DATE column, or process it in an UPDATE statement in step 2 itself.
- Drop the old column.
- Rename the new column to the old column.
UPDATE Add demo
Customization
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>
Add new column:
SQL> ALTER TABLE t ADD (dt DATE);
Table altered.
SQL>
Make the required update
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>
Check:
SQL> SELECT * FROM t;
YMD DT
-------- ---------
20101112 12-NOV-10
20150101 01-JAN-15
20160101
SQL>
Dropping the old column:
SQL> ALTER TABLE t DROP COLUMN ymd;
Table altered.
SQL>
Rename the new column to the name of the old column
SQL> ALTER TABLE t RENAME COLUMN dt TO ymd;
Table altered.
SQL>
You just fixed the problem
SQL> SELECT * FROM t;
YMD
---------
12-NOV-10
01-JAN-15
SQL>
source to share