Datetime conversion in WHERE clause
От | Philippe Lang |
---|---|
Тема | Datetime conversion in WHERE clause |
Дата | |
Msg-id | 6C0CF58A187DA5479245E0830AF84F420803F0@poweredge.attiksystem.ch обсуждение исходный текст |
Ответы |
Re: Datetime conversion in WHERE clause
|
Список | 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
В списке pgsql-sql по дате отправления: