to_date problems (Re: Favor for Postgres User at WSI)

Kate Collins <klcollins(at)wsicorp(dot)com> writes:
>> In other words it is defaulting to the year 0 (actually year 1 BC, since
>> there is no year 0) instead of 2000.

Hmm, you're right:

regression=# select to_date( '001112', 'YYMMDD');
to_date
---------------
0001-11-12 BC
(1 row)

>> Now I run the equivalent select statement on Oracle, and I get:
>> select to_char( to_date( '001112', 'YYMMDD'), 'YYYYMMDD') from dual;
>> TO_CHAR(TO_DATE('001112','YYMMDD'),'YYYYMMDD')
>> ---------------------------------------------------------------------------
>> 20001112
>>
>> Which is what I expect.
>>
>> Is "YY" suppose to default to the current century or is this an
>> Oracle'ism?

I dunno whether there is any actual spec for to_date(), but I do agree
that if you've specified a 2-digit YY format, something 2000-centric
would be more useful than the current behavior.

It doesn't seem to be doing anything particularly sensible with a
4-digit date, either:

regression=# select to_date( '00001112', 'YYYYMMDD');
to_date
------------
1112-11-12
(1 row)

This case I *would* have expected to produce 1 BC, but nope...

>> BTW, on postgres, when I try:
>> select to_char( to_date( '20001112', 'YYYYMMDD'), 'YYYYMMDD');
>> I get the error: "ERROR: Unable to convert timestamp to date"

That seems broken in current sources, too:

regression=# select to_date( '20001112', 'YYYYMMDD');
ERROR: Unable to convert timestamp to date

Looks like you've rooted out a number of problems in to_date (which
in fairness is new-in-7.0 code). I've cc'd this to to_date's author,
whom I hope will find a fix for 7.1.

BTW, direct conversion to timestamp does something sensible in all
these cases:

regression=# select '001112'::timestamp;
?column?
------------------------
2000-11-12 00:00:00-05
(1 row)

regression=# select '00001112'::timestamp;
?column?
---------------
0001-11-12 BC
(1 row)

regression=# select '20001112'::timestamp;
?column?
------------------------
2000-11-12 00:00:00-05
(1 row)

so it doesn't seem to be the fault of the underlying timestamp or
date datatypes ...

regards, tom lane