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
Thomas, try this:
'2013-02-31'::date--
On Mon, Jan 20, 2014 at 7:44 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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