to_date conversion semantics?

On Monday 20 September 2010 7:20:29 am Colin 't Hart wrote:

Hi,

I can't find in the Postgresql documentation the semantics that explains
the following:

colin@ruby:~/workspace/eyedb$ psql
psql (8.4.4)
Type "help" for help.

colin=> select to_date('731332', 'YYMMDD');
to_date
------------
1974-02-01
(1 row)

colin=>

Thanks,

Colin

http://www.postgresql.org/docs/8.4/interactive/functions-formatting.html

--
Adrian Klaver
adrian.klaver@gmail.com

I must be blind, I can see the syntax but I can't see where it explains the
wrapping phenomenon that I'm seeing.

Cheers,

Colin

On 20 September 2010 16:36, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 09/20/2010 07:50 AM, Colin 't Hart wrote:

I must be blind, I can see the syntax but I can't see where it explains
the wrapping phenomenon that I'm seeing.

Cheers,

Colin

My turn to be blind, what wrapping ? :)

--
Adrian Klaver
adrian.klaver@gmail.com

The 32nd of Undecember (!) turning into the 1st of February of the
next year... instead of throwing an exception like I expect.

"Colin 't Hart" <colinthart@gmail.com> writes:

The 32nd of Undecember (!) turning into the 1st of February of the
next year... instead of throwing an exception like I expect.

As mentioned previously, to_date isn't the tool to use if you want
a strict conversion --- it's designed to be rather, um, liberal.
Some people think that accepting dates like June 31st is a feature.

regards, tom lane

On 20/09/10 20:18, Colin 't Hart wrote:

The 32nd of Undecember (!) turning into the 1st of February of the
next year... instead of throwing an exception like I expect.

What Tom said, but it's presumably using mktime(...) somewhere internally.

perl -MPOSIX -e 'print scalar gmtime(mktime(0,0,0,32,13-1,73)),"\n"'
Fri Feb 1 00:00:00 1974

http://perldoc.perl.org/POSIX.html#mktime
http://linux.die.net/man/3/mktime

--
Richard Huxton
Archonet Ltd