Re: Junk date getting uploaded into date field

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