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;
