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

On Tue, 7 Nov 2000, Tom Lane wrote:

> 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...

Where is *guarantee* that the year is 4-digits?!

test=# select '123456-11-12'::date;
?column?
--------------
123456-11-12
(1 row)

The to_char/timestamp not try expect that YYYY = 4-digits (see docs)

test=# select to_char('123456-11-12'::timestamp, 'YYYY DD/Mon');
to_char
---------------
123456 12/Nov
(1 row)

I mean that use in to_date() year in the first position is not good
idea, better is try:

test=# select to_date('11122000', 'MMDDYYYY');
to_date
------------
2000-11-12

And 'YY' - it's hell, what is '00'? ... 1900 or 2000 or 20000?

>
> >> 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

Yes, because to_date() expect that year is '20001112' and internal
PG's date/time routines disable convert it.

test=# select to_date( '2000-1112', 'YYYY-MMDD');
to_date
------------
2000-11-12

> 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.

How? Create limit for YYYY to 4-digits?

> 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)

Why not 1900?

test=# select to_char('1900-11-12'::date, 'YYDDMM')::timestamp;
?column?
------------------------
2000-12-11 00:00:00+01
(1 row)

What is right here? Really '00' = 2000? .... but input is 1900

> 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)

Well, thy use this logic for greater year:

test=# select '20000-11-12'::timestamp;
?column?
---------------------
20000-11-12 00:0000
(1 row)

test=# select '200001112'::timestamp;
ERROR: Bad timestamp external representation '200001112'
test=#

???

Well, possible solution: to_timestamp/date see if in the input is
some separator (non-digit char) between YYYY and next template (like DD),
if separator not here to_date() will expect 4-digits year.

'20001112' not separator --> 4-digits for year = 2000
'20000-11-12' separator '-' --> read it as 20000

'200001112' not separator --> 4-digits for year = 2000
--> 2-digits for month = 01
--> 2-digits for day = 11
--> last '2' is ignored

With 'YY' *hell* I not sure... add current year IMHO not must be
correct. I mean that correct solution is:

test=# select to_date('00-12-11', 'YY-DD-MM');
ERROR: Can't convert 'YY' to a real year.

But if users want for their Oracle->PG port use 'YY' as last two digits
in the current year, not problem make it....

Karel