Sorry for creating the confusion. The table drawn was PostgreSQL vs EDB Advanced Server. Thanks Burce for clarification.
For the 1-digit, 2-digit & 3-digit Year inputs, as I said, I didn't see any document in PG which will explain what would be the century considered if it is not given. If I missed out it somewhere please let me know. I refer to following link which explains the behavior of Oracle. http://forums.oracle.com/forums/thread.jspa?threadID=312239 Now, if 1. # of digits given is greater than the # of Ys i.e. postgres=# select to_date('01-jan-111', 'DD-MON-Y'); to_date ------------ 2111-01-01 (1 row) What we should do ? Either we should throw an error or we should give what user has provided. IMHO, we should follow what format is given by user. However, even if the 'format' gets wrong rather invalid, it is not throwing any error. e.g. postgres=# select to_date('01-jan-111', 'DD-MON-Y POSTGRES'); <<<<---- Look at this to_date ------------ 2111-01-01 (1 row) 2. # of digits given is less than the # of Ys Consider following case, postgres=# select to_date('01-jan-6', 'DD-MON-Y'), to_date('01-jan-6', 'DD-MON-YYYY'); to_date | to_date ------------+------------ 2006-01-01 | 0006-01-01 Why this behaviour not predictable ? I think we are always considering the current century, if it is not provided. If I missed out any related document, please share. And yes, postgres=# select to_date('01-jan-1761', 'DD-MON-Y'); to_date ------------ 3761-01-01 <<<----- Look at this. (1 row) Definitely, their is a bug in this case. Am I missing something ? -Piyush On Thu, Mar 17, 2011 at 3:30 AM, Bruce Momjian <br...@momjian.us> wrote: > Robert Haas wrote: > > On Wed, Mar 16, 2011 at 5:52 PM, Bruce Momjian <br...@momjian.us> wrote: > > > Robert Haas wrote: > > >> On Wed, Mar 16, 2011 at 8:21 AM, Piyush Newe > > >> <piyush.n...@enterprisedb.com> wrote: > > >> > Data Format ?? ? ? ? ? ? ? ?PostgreSQL EDBAS > > >> > TO_DATE('01-jan-10', ?'DD-MON-Y') ?? ? ? ?2010-01-01 Error > > >> > TO_DATE('01-jan-10', ?'DD-MON-YY') ?? ? ? ?2010-01-01 01-JAN-2010 > > >> > TO_DATE('01-jan-10', ?'DD-MON-YYY') 2010-01-01 01-JAN-2010 > > >> > TO_DATE('01-jan-10', ?'DD-MON-YYYY') 0010-01-01 01-JAN-0010 > > >> > In this case, it seems in last 3 cases PG is behaving correctly. > Whereas in > > >> > 1st case the output is not correct since the Format ('Y') is lesser > than the > > >> > actual input ('10'). But PG is ignoring this condition and throwing > whatever > > >> > is input. The output year is might not be the year, what user is > expecting. > > >> > Hence PG should throw an error. > > >> > > >> I can't get worked up about this. ?If there's a consensus that > > >> throwing an error here is better, fine, but on first blush the PG > > >> behavior doesn't look unreasonable to me. > > >> > > >> > Data Format ?? ? ? ? ? ? ? ?PostgreSQL EDBAS > > > > > > To clarify, the user is reporting EDB Advanced Server, though the > > > community PG has the same issues, or at least similar; ?with git HEAD: > > > > > > ? ? ? ?test=> SELECT TO_DATE('01-jan-2010', ?'DD-MON-YY'); > > > ? ? ? ? ?to_date > > > ? ? ? ?------------ > > > ? ? ? ? 3910-01-01 > > > ? ? ? ?(1 row) > > > > Actually, I think he's comparing PostgreSQL to Advanced Server. > > Oh, I understand now. I was confused that the headings didn't line up > with the values. I see now the first value is community PG and the > second is EDBAS. > > -- > Bruce Momjian <br...@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + > -- -- Piyush S Newe Principal Engineer EnterpriseDB office: +91 20 3058 9500 www.enterprisedb.com Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
