Re: Rectifying wrong Date outputs

Bruce Momjian wrote:
> Piyush Newe wrote:
> > Hi,
> >
> > I was randomly testing some date related stuff on PG & observed that the
> > outputs were wrong.
> >
> > e.g.
> > postgres=# SELECT TO_DATE('01-jan-2010', 'DD-MON-YY');
> > to_date
> > ------------
> > 3910-01-01 <--------- Look at this
> > (1 row)
> >
> > postgres=# SELECT TO_DATE('01-jan-2010', 'DD-MON-YYYY');
> > to_date
> > ------------
> > 2010-01-01
> > (1 row)
>
> I have done some work on this problem, and have developed the attached
> patch. It genarates the output in the final column of this table:
>
> Oracle PostgreSQL With PG Patch
> 1 TO_DATE('01-jan-1', 'DD-MON-Y') 01-JAN-2011 01-JAN-2001 01-JAN-2001+
> 2 TO_DATE('01-jan-1', 'DD-MON-YY') 01-JAN-2001 01-JAN-2001 01-JAN-2001
> 3 TO_DATE('01-jan-1', 'DD-MON-YYY') 01-JAN-2001 01-JAN-2001 01-JAN-2001
> 4 TO_DATE('01-jan-1', 'DD-MON-YYYY') 01-JAN-0001 01-JAN-0001 01-JAN-0001
> 5 TO_DATE('01-jan-10', 'DD-MON-Y') Error 01-JAN-2010 01-JAN-2010
> 6 TO_DATE('01-jan-10', 'DD-MON-YY') 01-JAN-2010 01-JAN-2010 01-JAN-2010
> 7 TO_DATE('01-jan-10', 'DD-MON-YYY') 01-JAN-2010 01-JAN-2010 01-JAN-2010
> 8 TO_DATE('01-jan-10', 'DD-MON-YYYY') 01-JAN-0010 01-JAN-0010 01-JAN-0010
> 9 TO_DATE('01-jan-067', 'DD-MON-Y') Error 01-JAN-2067 01-JAN-2067
> 10 TO_DATE('01-jan-111', 'DD-MON-YY') 01-JAN-0111 01-JAN-2011 01-JAN-2111*+
> 11 TO_DATE('01-jan-678', 'DD-MON-YYY') 01-JAN-2678 01-JAN-1678 01-JAN-1678+
> 12 TO_DATE('01-jan-001', 'DD-MON-YYYY') 01-JAN-0001 01-JAN-0001 01-JAN-0001
> 13 TO_DATE('01-jan-2010', 'DD-MON-Y') Error 01-JAN-4010 01-JAN-2010*
> 14 TO_DATE('01-jan-2010', 'DD-MON-YY') 01-JAN-2010 01-JAN-3910 01-JAN-2010*
> 15 TO_DATE('01-jan-2010', 'DD-MON-YYY') Error 01-JAN-3010 01-JAN-2010*
> 16 TO_DATE('01-jan-2010', 'DD-MON-YYYY') 01-JAN-2010 01-JAN-2010 01-JAN-2010

In an attempt to make the to_date/to_timestamp behavior documentable, I
have modified the patch to have dates adjust toward the year 2020, and
added code so if four digits are supplied, we don't do any adjustment.
Here is the current odd behavior, which is fixed by the patch:

test=> select to_date('222', 'YYY');
to_date
------------
2222-01-01
(1 row)

test=> select to_date('0222', 'YYY');
to_date
------------
2222-01-01
(1 row)

If they supply a full 4-digit year, it seems we should honor that, even
for YYY. YYYY still does no adjustment, and I doubt we want to change
that:

test=> select to_date('222', 'YYYY');
to_date
------------
0222-01-01
(1 row)

test=> select to_date('0222', 'YYYY');
to_date
------------
0222-01-01
(1 row)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +