Thread: to_date gives odd results

to_date gives odd results

From

Robert Treat

Date:

30 August 2007, 21:17:38

example from docs:
pagila=# select to_date('05 Dec 2000', 'DD Mon YYYY');
to_date
------------
2000-12-05
(1 row)

slight modification:
pagila=# select to_date('05 December 2000', 'DD Month YYYY');
to_date
---------------
0001-12-05 BC
(1 row)

I can't imagine that's expected behavior.... bug?
oh,

pagila=# select version();
version
-------------------------------------------------------------
PostgreSQL 8.2.4 on i386-pc-solaris2.10, compiled by cc -Xa
(1 row)

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Re: to_date gives odd results

From

Tom Lane

Date:

31 August 2007, 01:15:53

Robert Treat <xzilla@users.sourceforge.net> writes:
> pagila=# select to_date('05 December 2000', 'DD Month YYYY');
> to_date
> ---------------
> 0001-12-05 BC
> (1 row)

> I can't imagine that's expected behavior.... bug?

You needed to say FMMonth, else it expects fixed-width column.

to_date and friends are fairly awful in terms of not throwing errors
when the input doesn't really match the format.  I think what you
shoulda got here is a bad-input error.  However, somebody's going to
have to do a major rewrite of formatting.c to make it much better...

(Hmmm ... actually, in CVS HEAD this produces 2000-12-05 with or without
FM, which looks like a rather ill-considered change to try to make
things work "nicely".  The problem that it goes south on actually bad
input is still there.)

            regards, tom lane

Re: to_date gives odd results

From

Robert Treat

Date:

31 August 2007, 01:36:14

On Thursday 30 August 2007 21:15, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > pagila=# select to_date('05 December 2000', 'DD Month YYYY');
> > to_date
> > ---------------
> > 0001-12-05 BC
> > (1 row)
> >
> > I can't imagine that's expected behavior.... bug?
>
> You needed to say FMMonth, else it expects fixed-width column.

Wow, I don't get that reading the docs. It says "FM suppresses leading zeroes
and trailing blanks that would otherwise be added to make the output of a
pattern be fixed-width."   The word output led me to believe that had
something to do with what would be displayed, not interpreting information
given to the function. Guess it could be argued this is a display issue
somehow.

>
> to_date and friends are fairly awful in terms of not throwing errors
> when the input doesn't really match the format.  I think what you
> shoulda got here is a bad-input error.  However, somebody's going to
> have to do a major rewrite of formatting.c to make it much better...
>
> (Hmmm ... actually, in CVS HEAD this produces 2000-12-05 with or without
> FM, which looks like a rather ill-considered change to try to make
> things work "nicely".  The problem that it goes south on actually bad
> input is still there.)
>

ISTR to_date and friends are designed to be oracle compatable, so I'd guess
that change was made in an effort to mimic oracle, which returns the same
value for both syntax's I used originally.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Re: to_date gives odd results

From

"Josh Tolley"

Date:

31 August 2007, 08:58:43

> On Thursday 30 August 2007 21:15, Tom Lane wrote:
> > to_date and friends are fairly awful in terms of not throwing errors
> > when the input doesn't really match the format.  I think what you
> > shoulda got here is a bad-input error.  However, somebody's going to
> > have to do a major rewrite of formatting.c to make it much better...

Any votes for making that formatting.c rewrite a TODO item?

-eggyknap

Re: to_date gives odd results

From

Alvaro Herrera

Date:

31 August 2007, 13:33:18

Josh Tolley escribió:
> > On Thursday 30 August 2007 21:15, Tom Lane wrote:
> > > to_date and friends are fairly awful in terms of not throwing errors
> > > when the input doesn't really match the format.  I think what you
> > > shoulda got here is a bad-input error.  However, somebody's going to
> > > have to do a major rewrite of formatting.c to make it much better...
>
> Any votes for making that formatting.c rewrite a TODO item?

Well, there is already a to_char patch scheduled for 8.4.  If you want
to improve the to_date code, you are invited to do so -- no need to have
a TODO item about it.

If what you expect is that having a TODO item will mean that somebody
else will start working on it, I think you'll be disappointed :-)

(OTOH maybe we should add it and put the % mark in it.)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: to_date gives odd results

From

"Josh Tolley"

Date:

31 August 2007, 13:35:36

On 8/31/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Josh Tolley escribi=F3:
> > > On Thursday 30 August 2007 21:15, Tom Lane wrote:
> > > > to_date and friends are fairly awful in terms of not throwing errors
> > > > when the input doesn't really match the format.  I think what you
> > > > shoulda got here is a bad-input error.  However, somebody's going to
> > > > have to do a major rewrite of formatting.c to make it much better...
> >
> > Any votes for making that formatting.c rewrite a TODO item?
>
> Well, there is already a to_char patch scheduled for 8.4.  If you want
> to improve the to_date code, you are invited to do so -- no need to have
> a TODO item about it.

I figured as much.

> If what you expect is that having a TODO item will mean that somebody
> else will start working on it, I think you'll be disappointed :-)

I realize this chance is slim. The likelihood that I could get to it
and make something useful of it seemed even more slim :)

> (OTOH maybe we should add it and put the % mark in it.)

- Josh