Text->Date conversion in a WHERE clause

From: Collin Peters <cadiolis(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Text->Date conversion in a WHERE clause
Date: 2005-10-12 20:59:26
Message-ID: df01c91b0510121359m1d7e67b5n41ee4d939798b69b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have a table that has some columns which store 'custom' fields so the
content varies according to the user that the row belongs to. For one
of the groups of users the field is a date (the type of the field is
'text' though). I'm trying to perform a query where it only returns
values in a certain date range so in the WHERE clause I have

WHERE cust3 <> ''
AND cust3::text::timestamp > CURRENT_DATE - interval '1 month'

This results in the error 'ERROR: date/time field value out of range:
"052-44-5863"'. Now that is obviously not a valid date.... but there
is actually more to the where clause and the first part of it excludes
all rows where the user is not even the correct type, so the row which
includes the field '052-44-5863' should really not even be checked.

My main confusion lies in the assumption I made that the offending row
would not even be included as it should have already been discarded.
Is this not the case? How can I overcome this problem? There
appears to be no isDate() function in postgresql like there is in sql
server.

Regards,
Collin Peters

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Michael Fuhr 2005-10-12 21:03:37 Re: regular expression
Previous Message Andy 2005-10-12 13:52:23 Strange join...maybe some improvements???