Thread
-
Re: Rectifying wrong Date outputs
Bruce Momjian <bruce@momjian.us> — 2011-09-05T23:32:27Z
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. +