|
|
|
Junior Member
 
Group: Forum Members
Last Login: 6/18/2007 5:02:00 PM
Posts: 18,
Visits: 1
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/9/2008 2:55:55 PM
Posts: 185,
Visits: 2
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 6/18/2007 5:02:00 PM
Posts: 18,
Visits: 1
|
|
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.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/9/2008 2:55:55 PM
Posts: 185,
Visits: 2
|
|
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
|
|
|
|