to_date() and invalid dates

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

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

Thomas, try this:

'2013-02-31'::date
--
Mike Nolan

On Mon, Jan 20, 2014 at 7:44 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:

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

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

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

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

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

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

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
/messages/by-id/20099.1350484290@sss.pgh.pa.us
that this is known and accepted behaviour.
However,
/messages/by-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

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