NEED TO CHECK FORMAT TO 'YYYY-MM-DD'

UDA

NEED TO CHECK FORMAT TO 'YYYY-MM-DD'

Hi,
I have a column which is of type varchar(20) and which holds date value. Now i need to verify whether the data in the column is having correct date format.
Date format should be YYYY-MM-DD.

Thanks,
rlaskar
3 REPLIES

Re: NEED TO CHECK FORMAT TO 'YYYY-MM-DD'

There are two ways to do this that I know of. One is to write a UDF. There is currently a UDF available with the Oracle UDF's called "toDate" that converts a character column to a date. It produces an error if the date cannot convert. However, you can easily modify it to return a NULL if the column does not contain a valid date which is what I did with it.

Secondly, you could join to the SYS_CALENDAR table as follows:

sel CALENDAR_DATE
FROM your_table yt,
LEFT OUTER JOIN SYS_CALENDAR.CALENDAR CAL
ON (CALENDAR_DATE (FORMAT 'YYYY-MM-DD') (CHAR(10))) = yt.mydate;

If the date is valid, you will get the result in a DATE data type. If the date is invalid, you will get a NULL back.

Good luck.

Barry

Re: NEED TO CHECK FORMAT TO 'YYYY-MM-DD'

Thanks. But it's still not working for me.

The column that i have is of type varchar(20) and it have value as "ABCDEFGH".
If I ran the above query it returns me all the rows from the table.
Say Field ABC of type varchar(20) is having values like
2006/01/01
2006_01_01
abcdehgjih
2001-01-01
01-02-2006 and so on.

I want to find the invalid date not the one that is valid. So my SQL should return only abcdehgjih as invalid date.

Re: NEED TO CHECK FORMAT TO 'YYYY-MM-DD'

Modify the query to the following:

sel yt.mydate
FROM your_table yt,
LEFT OUTER JOIN SYS_CALENDAR.CALENDAR CAL
ON (CALENDAR_DATE (FORMAT 'YYYY-MM-DD') (CHAR(10))) = yt.mydate
WHERE CALENDAR_DATE IS NULL;

Note that this is only considering the date to be valid if it's in the YYYY-MM-DD format. In order to check the other formats, you would have to do additional left outer joins to the date table with the various formats.

Thanks,
Barry