ElevateDB: Function isDate()

I am without a doubt spoiled by some of Microsoft SQL Server's built in functions. One such built in function that I have used many times is the isDATE function. This function let's you test whether a data value represents a date.

ElevateDB does not have an isDATE function, so I built one and will show you the process I went through to make one.

First I fired up the EDB Manager and created a new script that I could code and test as I went. I decided to see if I could use a BEGIN ... EXCEPTION ... END block to build this function. According to the documentation

Use these statements to declare a block of statements for execution in a procedure or function with an associated exception block of statements for handling any exceptions that may occur in the block of statements.

My intent was to "force an error" by trying to cast a string as a date and trap for an error if the string did not represent a date. It worked. The only gotcha was this, when I executed the code inside an EDB Manager script window, EDB Manager stopped execution to tell me that there was a conversion error with the line

SET TestDate = CAST(strDate as Date);

ElevateDB Error #1011 An error occurred with the value 12345678 (A conversion error occurred)

I will use the error number 1011 in the exception handling.

I fully expected an error to occur but I didn't know the error number ahead of time. I then clicked continue and the program stopped at the breakpoint I had set for SET X = 1; When I inspected the local variables, I saw that the script had done exactly what I asked. It set the isDATE value to 0 (meaning not a date). When I toggle between commenting one of the SET strDate = ... lines, the script perfoms correctly.

Copy this code into a new SCRIPT window inside EDB Manager and set a break point at the line
SET X = 1;.

Notice the second SET strDate statement has been commented out
--SET strDate = '1959-03-23'; commented out.

You can toggle between commenting out both of these SET strDate statements to see how EDB Manager handles good and bad date data.

SCRIPT
BEGIN
----------------------------------------------------------------
-- X is used set a breakpoint within ED Manager
----------------------------------------------------------------
DECLARE X INTEGER;

DECLARE strDate varchar(20);
DECLARE TestDate DATE;
DECLARE isDATE INTEGER;
DECLARE ErrCode Integer;

SET isDATE = 1;
SET strDate = '12345678';--SET strDate = '1959-03-23';
SET ErrCode = 0;

BEGIN
SET TestDate = CAST(strDate as Date);
EXCEPTION
   SET ErrCode = ERRORCODE();
   IF ERRORCODE()=1011 THEN
     SET isDATE = 0;
   END IF;
END;
SET X = 1;
END

Converting the above script into an actual function is pretty straight forward. From inside EDB Manager open a new SQL window and copy the following code:
br />

CREATE FUNCTION "isDate"  (INOUT "DateStr" VARCHAR(20) COLLATE UNI)
RETURNS INTEGER

BEGIN
DECLARE TestDate DATE;
DECLARE ValidDate INTEGER;

SET DateStr = COALESCE(DateStr,'');
SET ValidDate = 1;

BEGIN
SET TestDate = CAST(DateStr as Date);
EXCEPTION
   IF ERRORCODE()=1011 THEN
     SET ValidDate = 0;
   END IF;
END;

RETURN ValidDate;
END

This ElevateDB isDATE function is not as robust as the MS SQL Server function but it does allow you to test whether the date value passed in represents a date. Enjoy.

Semper Fi,
Gunny Mike