Thread: This is my first template
Hi folks Looks like there's something I'm not understanding about date/time queries on a date field. I have a param query that includes the statement: AND active_from_date <= $2 where $2 is a well-formed ISO date. Query works as expected when there are records in the result set. When the result set is empty, PG throws an error: date/time field value out of range: "2011-4-31" I'm using the default datestyle setting, which should work here, I think? Experimenting with other settings hasn't helped. This query runs hundreds of times a day and I don't want the error clogging up my logs. But I don't want to suppress the error as the query is business-critical and I need to know if anything goes wrong. I'd welcome suggestions as to how I can get this query to run without throwing a date/time error when the result set is empty. Thanks... -- Geoff Caplan Uviva Ltd
Attachment
On Tue, Apr 21, 2009 at 12:02:02PM +0100, Geoff Caplan wrote: > Looks like there's something I'm not understanding about > date/time queries on a date field. [...] > date/time field value out of range: "2011-4-31" As far as I'm aware; April only has 30 days. You're asking PG for an invalid date and this is its way of telling you so! -- Sam http://samason.me.uk/
On Tue, Apr 21, 2009 at 7:02 AM, Geoff Caplan <geoff@uviva.com> wrote: > Hi folks > > Looks like there's something I'm not understanding about date/time queries > on a date field. > > I have a param query that includes the statement: > > AND active_from_date <= $2 > > where $2 is a well-formed ISO date. > > Query works as expected when there are records in the result set. When the > result set is empty, PG throws an error: > > date/time field value out of range: "2011-4-31" "2011-4-31" is not a well formed ISO date. merlin
There are 30 days in April.
On Apr 21, 2009 2:34pm, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Apr 21, 2009 at 7:02 AM, Geoff Caplan geoff@uviva.com> wrote:
>
> > Hi folks
>
> >
>
> > Looks like there's something I'm not understanding about date/time queries
>
> > on a date field.
>
> >
>
> > I have a param query that includes the statement:
>
> >
>
> > AND active_from_date
> >
>
> > where $2 is a well-formed ISO date.
>
> >
>
> > Query works as expected when there are records in the result set. When the
>
> > result set is empty, PG throws an error:
>
> >
>
> > date/time field value out of range: "2011-4-31"
>
>
>
> "2011-4-31" is not a well formed ISO date.
>
>
>
> merlin
>
>
>
> --
>
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>
> To make changes to your subscription:
>
> http://www.postgresql.org/mailpref/pgsql-general
>
On Apr 21, 2009 2:34pm, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Apr 21, 2009 at 7:02 AM, Geoff Caplan geoff@uviva.com> wrote:
>
> > Hi folks
>
> >
>
> > Looks like there's something I'm not understanding about date/time queries
>
> > on a date field.
>
> >
>
> > I have a param query that includes the statement:
>
> >
>
> > AND active_from_date
> >
>
> > where $2 is a well-formed ISO date.
>
> >
>
> > Query works as expected when there are records in the result set. When the
>
> > result set is empty, PG throws an error:
>
> >
>
> > date/time field value out of range: "2011-4-31"
>
>
>
> "2011-4-31" is not a well formed ISO date.
>
>
>
> merlin
>
>
>
> --
>
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>
> To make changes to your subscription:
>
> http://www.postgresql.org/mailpref/pgsql-general
>
Geoff Caplan wrote: > where $2 is a well-formed ISO date. > > Query works as expected when there are records in the result set. When > the result set is empty, PG throws an error: > > date/time field value out of range: "2011-4-31" Is April 31st really a well-formed ISO date? I wonder what jhey smoke there in ISO meetings. My guess is that you're doing date arithmetic wrong somewhere. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.