Oracle IsDate() Function

As promised in a previous post here is my IsDate() function for Oracle

I don't know how great a function it is, since I am a novice to Oracle, but it seems to work pretty well and very quickly. I learned a lot while writing it, especially about basic excpetion handling in PL/SQL. I'm really not a big fan of Oracle, or PL/SQL, but I need to know it for my work, so I hope that my struggles in things like this can help others fuddle through.

Normally, I would use ColdFusions IsDate() function and be VERY happy with it. But in this case Iam importing batches of 1,000,000 records from raw data files and running conversion scripts on them, and ColdFusion just isn't built for that kind of work, so I am doing it directly in Oracle with PL/SQL and then calling that from ColdFusion. So far so good. It is a long road, since Iam new to Oracle there is a large learning curve for me.

This Oracle function is pretty basic. It takes in a varchar2 and returns a varchar2(I would return a Boolean, but for whatever reason, in Oracle you don't get Boolean values for your $65,000 investment, and I cannot convert 1 and 0 to boolean. Stoopid Oracle). The it is the string that we would like to test.


        create or replace
            function IsDate (str varchar2) return varchar2 is
            inDate varchar2(40);
            
    

Next we add a sub-function called dateCheck(). This takes in two parameters and returns one, all are varchar2 (see rant above). The first IN value is the string we passed into the parent function witht he string we want to test. The second IN parameter is the mask of the date format we want to check the string against. The return string will be either 'true' or 'false'.


         FUNCTION dateCheck (
             inputDate varchar2, inputMask varchar2)
             RETURN varchar2
             IS
             dateVar date;
             BEGIN
             dateVar:= to_date(inputDate,inputMask);
             return 'true';
            
             exception
             when others then
             return 'false';
             END;
    

This sub-function uses the to_date() funciton in Oracle. to_date() tries to read in a string and convert it to a date using a user specified input mark. For example, to_date(06-18-2008, 'mm-dd-yyyy') would return a valid date, but to_date(06-18-2008, 'mm.dd.yyyy') would raise an exception. So since Oracle cannot return troe or false, I have create my function to retrun 'true' if the command executes successfully and 'false' if an exception is raised.

Finally, my parent function will call the child function several times using different date pattern masks to see if any of them will work. If none of them evalute to true, the overall function will reutn 'false'. If one of them wrks, then it is a valid date and the function will return 'true'.


             inDate:= trim(str);
             if dateCheck(inDate, 'mm-dd-yyyy') = 'false'
             AND dateCheck(inDate, 'mm-dd-yy') = 'false'
             AND dateCheck(inDate, 'yyyy-mm-dd') = 'false'
             AND dateCheck(inDate, 'yy-mm-dd') = 'false'
             AND dateCheck(inDate, 'yyyy-mon-dd') = 'false'
             AND dateCheck(inDate, 'yy-mon-dd') = 'false'
             AND dateCheck(inDate, 'dd-mon-yyyy') = 'false'
             AND dateCheck(inDate, 'dd-mon-yy') = 'false'
             AND dateCheck(inDate, 'mmddyy') = 'false'
             AND dateCheck(inDate, 'mmddyyyy') = 'false'
             AND dateCheck(inDate, 'yyyymmdd') = 'false'
             AND dateCheck(inDate, 'yymmdd') = 'false'
             AND dateCheck(inDate, 'yymmdd') = 'false'
             AND dateCheck(inDate, 'yymondd') = 'false'
             AND dateCheck(inDate, 'yyyymondd') = 'false'
             AND dateCheck(inDate, 'mm/dd/yyyy') = 'false'
             AND dateCheck(inDate, 'yyyy/mm/dd') = 'false'
             AND dateCheck(inDate, 'mm/dd/yy') = 'false'
             AND dateCheck(inDate, 'yy/mm/dd') = 'false'
             AND dateCheck(inDate, 'mm.dd.yyyy') = 'false'
             AND dateCheck(inDate, 'mm.dd.yy') = 'false'
             AND dateCheck(inDate, 'yyyy.mm.dd') = 'false'
             AND dateCheck(inDate, 'yy.mm.dd') = 'false'
             then
             return 'false';
             else
             return 'true';
             end if;
    

So here is the overall code:


        create or replace
            function IsDate (str varchar2) return varchar2 is
            inDate varchar2(40);
            
             FUNCTION dateCheck (
             inputDate varchar2, inputMask varchar2)
             RETURN varchar2
             IS
             dateVar date;
             BEGIN
             dateVar:= to_date(inputDate,inputMask);
             return 'true';
            
             exception
             when others then
             return 'false';
             END;
            
            BEGIN
             inDate:= trim(str);
             if dateCheck(inDate, 'mm-dd-yyyy') = 'false'
             AND dateCheck(inDate, 'mm-dd-yy') = 'false'
             AND dateCheck(inDate, 'yyyy-mm-dd') = 'false'
             AND dateCheck(inDate, 'yy-mm-dd') = 'false'
             AND dateCheck(inDate, 'yyyy-mon-dd') = 'false'
             AND dateCheck(inDate, 'yy-mon-dd') = 'false'
             AND dateCheck(inDate, 'dd-mon-yyyy') = 'false'
             AND dateCheck(inDate, 'dd-mon-yy') = 'false'
             AND dateCheck(inDate, 'mmddyy') = 'false'
             AND dateCheck(inDate, 'mmddyyyy') = 'false'
             AND dateCheck(inDate, 'yyyymmdd') = 'false'
             AND dateCheck(inDate, 'yymmdd') = 'false'
             AND dateCheck(inDate, 'yymmdd') = 'false'
             AND dateCheck(inDate, 'yymondd') = 'false'
             AND dateCheck(inDate, 'yyyymondd') = 'false'
             AND dateCheck(inDate, 'mm/dd/yyyy') = 'false'
             AND dateCheck(inDate, 'yyyy/mm/dd') = 'false'
             AND dateCheck(inDate, 'mm/dd/yy') = 'false'
             AND dateCheck(inDate, 'yy/mm/dd') = 'false'
             AND dateCheck(inDate, 'mm.dd.yyyy') = 'false'
             AND dateCheck(inDate, 'mm.dd.yy') = 'false'
             AND dateCheck(inDate, 'yyyy.mm.dd') = 'false'
             AND dateCheck(inDate, 'yy.mm.dd') = 'false'
             then
             return 'false';
             else
             return 'true';
             end if;
            
             --exception
             --when others then return 'false';
            END;
    

Comments
Peter Dolina's Gravatar Excellent, it works...indeed Oracle has holes in functionality, I would not recommend this database at all.
# Posted By Peter Dolina | 9/6/11 5:04 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1. Contact Blog Owner