Thread: to_date() and invalid dates

Hi,

I asked this a while back already:

   select to_date('2013-02-31', 'yyyy-mm-dd');

will not generate an error (unlike e.g. Oracle)


However in the release notes of 9.2.3[1] it is mentioned that

  - Reject out-of-range dates in to_date() (Hitoshi Harada)

I tried the above statement using 9.2.6 and 9.3.2 in both versions 2013-02-03 is returned instead of rejecting the
input. 
The same is true if e.g. an invalid month is specified: to_date('2013-17-09', 'yyyy-mm-dd').

Does this check need a configuration setting to be in effect?

Regards
Thomas


[1] http://www.postgresql.org/docs/9.2/static/release-9-2-3.html

Thomas Kellerer wrote:
> I asked this a while back already:
> 
>    select to_date('2013-02-31', 'yyyy-mm-dd');
> 
> will not generate an error (unlike e.g. Oracle)

This is by design.

> However in the release notes of 9.2.3[1] it is mentioned that
> 
>   - Reject out-of-range dates in to_date() (Hitoshi Harada)
> 
> I tried the above statement using 9.2.6 and 9.3.2 in both versions 2013-02-03 is returned instead of
> rejecting the input.
> The same is true if e.g. an invalid month is specified: to_date('2013-17-09', 'yyyy-mm-dd').
> 
> Does this check need a configuration setting to be in effect?

This commit only rejects dates that are before 4713 BC and 5874898 AD,
which cannot be printed reasonably.

Yours,
Laurenz Albe

Albe Laurenz, 20.01.2014 15:29:
>> I asked this a while back already:
>>
>>    select to_date('2013-02-31', 'yyyy-mm-dd');
>>
>> will not generate an error (unlike e.g. Oracle)
>
> This is by design.

When I previously asked this question the answer as "this is based on Oracle's to_date()":
http://postgresql.1045698.n5.nabble.com/to-char-accepting-invalid-dates-td4598597.html#a4608551

Oracle rejects such a date, so this is highly irritating for users coming from Oracle.

>> However in the release notes of 9.2.3[1] it is mentioned that
>>
>>   - Reject out-of-range dates in to_date() (Hitoshi Harada)
>>
> This commit only rejects dates that are before 4713 BC and 5874898 AD,
> which cannot be printed reasonably.

Ah, then the comment is somewhat misleading, thanks for the clarification.


Regards
Thomas

Michael Nolan, 20.01.2014 16:17:
> Thomas, try this:
>
> '2013-02-31'::date

Thanks, I know this "works", but this can't be used if you have a non-ISO date string

On 01/20/2014 07:32 AM, Thomas Kellerer wrote:
> Michael Nolan, 20.01.2014 16:17:
>> Thomas, try this:
>>
>> '2013-02-31'::date
>
> Thanks, I know this "works", but this can't be used if you have a non-ISO date string
>
>

Hmm:

test=> SELECT '2013-02-31'::date;
ERROR:  date/time field value out of range: "2013-02-31"
LINE 1: SELECT '2013-02-31'::date;
                ^
test=> SELECT '2/31/2013'::date;
ERROR:  date/time field value out of range: "2/31/2013"
LINE 1: SELECT '2/31/2013'::date;
                ^
test=> SELECT '2/26/2013'::date;
     date
------------
  2013-02-26
(1 row)


>
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Thomas Kellerer wrote:
>>> I asked this a while back already:
>>>
>>>    select to_date('2013-02-31', 'yyyy-mm-dd');
>>>
>>> will not generate an error (unlike e.g. Oracle)
>>
>> This is by design.
> 
> When I previously asked this question the answer as "this is based on Oracle's to_date()":
> http://postgresql.1045698.n5.nabble.com/to-char-accepting-invalid-dates-td4598597.html#a4608551

See for example
http://www.postgresql.org/message-id/20099.1350484290@sss.pgh.pa.us
that this is known and accepted behaviour.
However,
http://www.postgresql.org/message-id/22259.1114613632@sss.pgh.pa.us
so I guess that it was not intended, but since it has been that way
for long enough it would be too painful to change it.

So actually my original answer "it is by design" is probably wrong.

"Based upon" should be read as "inspired by" rather than
"compatible with".

Yours,
Laurenz Albe