Re: Junk date getting uploaded into date field - Mailing list pgsql-general

From Albe Laurenz
Subject Re: Junk date getting uploaded into date field
Date
Msg-id A737B7A37273E048B164557ADEF4A58B17C56785@ntex2010i.host.magwien.gv.at
Whole thread Raw
In response to Re: Junk date getting uploaded into date field  (Thomas Kellerer <spam_eater@gmx.net>)
Responses Re: Junk date getting uploaded into date field
Re: Junk date getting uploaded into date field
List pgsql-general
Thomas Kellerer wrote:
> bsreejithin, 05.11.2013 13:14:
>> Not able to post the attached details as a comment in the reply box, so
>> attaching it as an image file :
>> <http://postgresql.1045698.n5.nabble.com/file/n5776987/Untitled.png>
> 
> It would have much easier if you had simply used copy & paste to post a text version of that SQL.
> Does your mail client not allow you to do that?
> 
> But your test case is essentially this:
> 
>    select to_date('33-OCT-2013', 'dd-mon-yyyy')
> 
> which indeed returns 2013-11-02 (using 9.3.1)
> 
> I don't know if this is inteded or actually a bug - I can't find anything in the docs relating to that
> behaviour.

There is a comment in utils/adt/formatting.c:

 * This function does very little error checking, e.g.
 * to_timestamp('20096040','YYYYMMDD') works

So at least this is not by accident.

On the other hand, I have always thought that these functions
are for Oracle compatibility, and sqlplus says:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> SELECT to_date('20096040','YYYYMMDD') FROM dual;
SELECT to_date('20096040','YYYYMMDD') FROM dual
               *
ERROR at line 1:
ORA-01843: not a valid month


I don't know if that should be fixed, but fixing it might break SQL
that deliberately uses the current behaviour.

Yours,
Laurenz Albe

pgsql-general by date:

Previous
From: Edson Richter
Date:
Subject: Re: Junk date getting uploaded into date field
Next
From: Reid Thompson
Date:
Subject: Re: Junk date getting uploaded into date field