MS SQL’s ISDATE function on Firebird

MS SQL has an ISDATE function that “returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0”. Although I never used it or had a scenario for it, I was porting some code from MS SQL to Firebird and this function was used, hence I had to create it.

As the documentation says it’s checking for valid date or time or datetime . I would say a better design would be to have separate functions for different types. And that’s also what I’m going to do in Firebird. You can create other variations easily or combine it to one.

recreate function is_date (val varchar(1000)) returns boolean as declare dummy date; begin begin begin dummy = cast(val as date); end when any do begin return false; end end return true; end

The idea is pretty simple. I try to cast the given “string” into a date and if that fails false is returned. Otherwise it’s true . I return boolean because Firebird supports this datatype. The extra begin - end is there so I can have the second return statement after when any (which is basically catch-all exception handler). Similarly with the assignment to dummy ; just to make Firebird happy.

Given the calling of internal function and user defined functions in Firebird is the same, there’s nothing preventing you to pretend it is an internal function.