Strict Date validation for Oracle

Tagged: Database, Oracle Date: 21st, November 2006

We had few problems with a huge database table (1600k rows of data). Basically problem was with invalid dates.

Our database is receiving data over serial port so there was Alot of garbage. Sice it’s such a huge table and there was so much invalid date formats I was forced to create decent Oracle replacement for VB function IsDate:

CREATE FUNCTION ISDATE( ip_date in varchar2 )
return date
is
begin
return to_date(ip_date,'dd-mm-yyyy');
exception
when others
then return null;
end isdate;

Only flaw I found was that you need to predefine date type you need… but we already knew that so it was not a problem.

Hope this helps someone!

2 Responses to “Strict Date validation for Oracle”

  1. Riz:
    January 5th, 2009 at 9:58 pm

    Thanks for the tip, very useful.

    Here’s how I modified it.

    CREATE OR REPLACE FUNCTION ISDATE
    (
    ip_date in varchar2,
    date_format in varchar2 default ‘yy-mm-dd’)
    return date
    is
    begin
    return to_date(ip_date,date_format);
    exception
    when others
    then return null;
    end isdate;

  2. Rupali:
    February 12th, 2010 at 8:20 pm

    Riz,

    The modification was explicitly excellent, i had been looking for osmething like this since long, very happy to see it (never thought the date format can be accepetd as a parameter , but I am new to Oracle anyway). Thanks a lot.

Leave a Reply