Re: Rectifying wrong Date outputs

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 <bruce(at)momjian(dot)us> wrote:

> Robert Haas wrote:
> > On Wed, Mar 16, 2011 at 5:52 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > > Robert Haas wrote:
> > >> On Wed, Mar 16, 2011 at 8:21 AM, Piyush Newe
> > >> <piyush(dot)newe(at)enterprisedb(dot)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 <bruce(at)momjian(dot)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.