Thread: Datetime conversion in WHERE clause

Hello,

I'm converting a varchar to a date in a search routine which looks like:

----------------------------------------------
CREATE FUNCTION public.search_data(varchar) RETURNS SETOF foo1 AS
' SELECT DISTINCT
 foo1.*
 FROM foo1 LEFT JOIN foo2 ON foo2.fk = foo1.pk
 WHERE    lower(foo1.text_data1) LIKE lower($1) OR lower(foo1.text_data2) LIKE lower($1) OR foo1.date_data =
$1::text:date
 OR lower(foo2.text_data3) LIKE lower($1) OR lower(foo2.text_data4) LIKE lower($1)
' LANGUAGE 'sql' VOLATILE;
----------------------------------------------

The problem with the ::text::date conversion is that the whole function
fails if the search string parameter cannot be converted into a valid
date, like 'test%', for example, which is valid for the other text
fields.

Is there a way to force the date conversion to fail sliently, and simply
return a null in case the parameter is not a valid date?

Thanks

Philippe Lang

Philippe,

> Is there a way to force the date conversion to fail sliently, and simply
> return a null in case the parameter is not a valid date?

You'd need to write a custom function, using an external language that allows 
you to test for valid date values.   I prefer using PL/perlU with 
Date::Manip, but use what you like.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco