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

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(at)uviva(dot)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(at)gmail(dot)com> wrote:
> On Tue, Apr 21, 2009 at 7:02 AM, Geoff Caplan geoff(at)uviva(dot)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(at)postgresql(dot)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.