tabs ↹ over ␣ ␣ ␣ spaces

by Jiří {x2} Činčura

MS SQL’s ISDATE function on Firebird

23 Jan 2020 Firebird, MS SQL Server, SQL

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.

Profile Picture Jiří Činčura is an independent developer focusing on data and business layers, language constructs, parallelism and databases. Specifically Entity Framework, asynchronous and parallel programming, cloud and Azure. He's Microsoft Most Valuable Professional and you can read his articles, guides, tips and tricks at www.tabsoverspaces.com.