Thread

Re: Rectifying wrong Date outputs

Bruce Momjian <bruce@momjian.us> — 2011-09-06T14:01:37Z

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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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