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

On Tue, 7 Nov 2000, Tom Lane wrote:

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

 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