All Forums UDA
rlaskar 18 posts Joined 03/06
31 Mar 2006
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

Barry-1604 176 posts Joined 07/05
31 Mar 2006

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

rlaskar 18 posts Joined 03/06
31 Mar 2006

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/012006_01_01abcdehgjih2001-01-0101-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.

Barry-1604 176 posts Joined 07/05
03 Apr 2006

Modify the query to the following:sel yt.mydateFROM your_table yt,LEFT OUTER JOIN SYS_CALENDAR.CALENDAR CALON (CALENDAR_DATE (FORMAT 'YYYY-MM-DD') (CHAR(10))) = yt.mydateWHERE 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

You must sign in to leave a comment.