Kate Collins <klcollins@wsicorp.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
