Datetime conversion in WHERE clause

From: "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Datetime conversion in WHERE clause
Date: 2004-09-09 14:23:10
Message-ID: 6C0CF58A187DA5479245E0830AF84F420803F0@poweredge.attiksystem.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Passynkov, Vadim 2004-09-09 15:01:38 Re: Isnumeric function?
Previous Message Jeff Eckermann 2004-09-09 14:02:06 Re: Isnumeric function?