Search Postgresql Archives

Re: Junk date getting uploaded into date field

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux