Re: to_date gives odd results

On Thursday 30 August 2007 21:15, Tom Lane wrote:
> Robert Treat <xzilla(at)users(dot)sourceforge(dot)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