Re: Rectifying wrong Date outputs

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

I marked with '*' every case where the patch doesn't match current PG,
and used a '+' to mark every case where it doesn't match Oracle.

I know Tom was worried that because the year field took more digits than
specified, it would prevent numeric columns from being pulled apart, but
our code has this check:

    if (S_FM(node->suffix) || is_next_separator(node))
    {
        /*
         * This node is in Fill Mode, or the next node is known to be a
         * non-digit value, so we just slurp as many characters as we can get.
         */
        errno = 0;
        result = strtol(init, src, 10);
    }

The reason these tests are accepting an unlimited number of digits is
because it is at the end of the string.  If you place a digit field
right after it, it will not use more characters than specified:

	test=> select to_date('9876', 'YYY');
	  to_date
	------------
	 9876-01-01
	(1 row)
	
	test=> select to_date('9876', 'YYYMM');
	  to_date
	------------
	 1987-06-01
	(1 row)

Yes, not documented, but I assume the coder was trying to be helpful.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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