In PostgreSQL (8.4), I'm trying to convert a string parameter into a date within a SQL query, falling back to now()
when the string is not a valid date (or empty).
In "pseudo-SQL", this would be something like this:
SELECT CASE WHEN ? is not a valid date THEN now()::DATE ELSE CAST(? AS DATE) END;
I've tried to simplify the problem to detect an empty string using these two queries:
SELECT CASE WHEN ?='' THEN now()::DATE ELSE CAST(? AS DATE) END;
SELECT DATE(CASE WHEN ?='' THEN now() ELSE ? END);
For example, if the parameter is ''
, this is equivalent to this:
SELECT CASE WHEN ''='' THEN now()::DATE ELSE CAST('' AS DATE) END;
SELECT DATE(CASE WHEN ''='' THEN now() ELSE '' END);
Both fail with ERROR: invalid input syntax for type timestamp with time zone: ""
It makes sense, but it implies that the ELSE
block is evaluated (or at least that its types are resolved) whether or not the CASE
condition is true. The following works, but what I'd like the CASE
(or similar) condition to handle, is precisely the case when it's not a valid date.
SELECT CASE WHEN '2011-12-01'='' THEN now()::DATE ELSE CAST('2011-12-01' AS DATE) END;
The closest I got to a working solution is this:
SELECT DATE(COALESCE(NULLIF(?, '')::timestamptz, now()));
In this case, if the parameter is ''
, it returns the current date, otherwise, it returns the date passed in the string parameter (provided it can be turned into a valid date).
What I would like is to go a step further and make anything that cannot be turned into a DATE
use the current date. I guess this could be done using a custom PL/pgSQL function that would trap this error, but can this be done without such a function, in "plain" SQL (or at least using the existing PostgreSQL functions)?