DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=sQpjStaGlMu3Fv2CerM4jXTzM4WfTKsjiLDh1YxRNuNZ2z/UhdZpvvZKvPxiRXyITlO2LBsXO+ePlfYoKnRFLqePQb7Pp6lcL1HQTZoImpEg7a1dXQ5FxCmqHNVk+nhqJq7RtEIxhGDOWEb65b/QKqZ8eFxdLKjPsymyH0VZxwM=
Message-ID: <df01c91b0510121359m1d7e67b5n41ee4d939798b69b@mail.gmail.com>
Date: Wed, 12 Oct 2005 13:59:26 -0700
From: Collin Peters <cadiolis@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Text->Date conversion in a WHERE clause
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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