From: Collin Peters <cadiolis@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Text->Date conversion in a WHERE clause
Date: Wed, 12 Oct 2005 13:59:26 -0700
Message-ID: <df01c91b0510121359m1d7e67b5n41ee4d939798b69b@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cadiolis@gmail.com
Subject: Re: Text->Date conversion in a WHERE clause
In-Reply-To: <df01c91b0510121359m1d7e67b5n41ee4d939798b69b@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox