Re: Junk date getting uploaded into date field

On 11/05/2013 05:29 AM, Albe Laurenz wrote:
> 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
>
>
I think the place for such warnings in addition to the source-code is in
the documentation. This or similar issues with to_date have popped up on
the lists a number of times.

Perhaps a "see warnings below" by the to_date description in table:
http://www.postgresql.org/docs/9.3/static/functions-formatting.html#FUNCTIONS-FORMATTING-TABLE

Then under usage notes something like:

The to_date and to_timestamp functions do minimal input error-checking
and are intended for conversion of non-standard formats that cannot be
handled by casting. These functions will attempt to convert illegal
dates to the best of their ability, e.g. to_date('33-OCT-2013',
'dd-mon-yyyy') will return 2013-11-02. Users of these functions are
advised to perform whatever external error-checking they deem prudent.

Cheers,
Steve