pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedto_char() accepting invalid dates?
9+ messages / 5 participants
[nested] [flat]
* to_char() accepting invalid dates?
@ 2011-07-18 08:22 Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 9+ messages in thread
From: Thomas Kellerer @ 2011-07-18 08:22 UTC (permalink / raw)
To: pgsql-sql
Hi,
I just noticed that to_char() will "accept" invalid dates such as 2011-02-31 and "adjust" them accordingly:
postgres=> select to_date('20110231', 'yyyymmdd');
to_date
------------
2011-03-03
(1 row)
is there a way to have to_date() raise an exception in such a case?
Regards
Thomas
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: to_char() accepting invalid dates?
@ 2011-07-18 09:23 Jasen Betts <jasen@xnet.co.nz>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 9+ messages in thread
From: Jasen Betts @ 2011-07-18 09:23 UTC (permalink / raw)
To: pgsql-sql
On 2011-07-18, Thomas Kellerer <spam_eater@gmx.net> wrote:
> Hi,
>
> I just noticed that to_char() will "accept" invalid dates such as 2011-02-31 and "adjust" them accordingly:
>
> postgres=> select to_date('20110231', 'yyyymmdd');
>
> to_date
> ------------
> 2011-03-03
> (1 row)
>
> is there a way to have to_date() raise an exception in such a case?
it's possible the odd behaviour you get is required by some standard.
however 'yyyymmdd' is a format that postgres understands natively,
so just cast the string to date.
jasen=# select '20110303'::date;
date
------------
2011-03-03
(1 row)
jasen=# select '20110231'::date;
ERROR: date/time field value out of range: "20110231"
LINE 1: select '20110231'::date;
^
postgres also understands YYYY-MM-DD and possilby a locale
dependant form with the year last
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: to_char() accepting invalid dates?
@ 2011-07-18 17:07 Thomas Kellerer <spam_eater@gmx.net>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 1 reply; 9+ messages in thread
From: Thomas Kellerer @ 2011-07-18 17:07 UTC (permalink / raw)
To: pgsql-sql
Jasen Betts wrote on 18.07.2011 11:23:
>> postgres=> select to_date('20110231', 'yyyymmdd');
>>
>> to_date
>> ------------
>> 2011-03-03
>> (1 row)
>>
>> is there a way to have to_date() raise an exception in such a case?
>
> it's possible the odd behaviour you get is required by some standard.
That would be *very* odd indeed.
> jasen=# select '20110303'::date;
Thanks for the tip, this was more a question regarding _why_ to_char() behaves this way.
Thomas
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: to_char() accepting invalid dates?
@ 2011-07-18 22:02 Bruce Momjian <bruce@momjian.us>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 2 replies; 9+ messages in thread
From: Bruce Momjian @ 2011-07-18 22:02 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
Thomas Kellerer wrote:
> Jasen Betts wrote on 18.07.2011 11:23:
> >> postgres=> select to_date('20110231', 'yyyymmdd');
> >>
> >> to_date
> >> ------------
> >> 2011-03-03
> >> (1 row)
> >>
> >> is there a way to have to_date() raise an exception in such a case?
> >
> > it's possible the odd behaviour you get is required by some standard.
>
> That would be *very* odd indeed.
>
>
> > jasen=# select '20110303'::date;
> Thanks for the tip, this was more a question regarding _why_ to_char() behaves this way.
Well, to_char() is based on Oracle's to_char(). How does Oracle handle
such a date?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: to_char() accepting invalid dates?
@ 2011-07-19 11:30 Thomas Kellerer <spam_eater@gmx.net>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 9+ messages in thread
From: Thomas Kellerer @ 2011-07-19 11:30 UTC (permalink / raw)
To: pgsql-sql
Bruce Momjian, 19.07.2011 00:02:
>>>> postgres=> select to_date('20110231', 'yyyymmdd');
>>>>
>>>> to_date
>>>> ------------
>>>> 2011-03-03
>>>> (1 row)
>>>>
>>>> is there a way to have to_date() raise an exception in such a case?
>>>
>>> it's possible the odd behaviour you get is required by some standard.
>>
>> That would be *very* odd indeed.
>>
>>
>>> jasen=# select '20110303'::date;
>> Thanks for the tip, this was more a question regarding _why_ to_char() behaves this way.
>
> Well, to_char() is based on Oracle's to_char(). How does Oracle handle
> such a date?
Oracle throws an error for the above example:
SQL> select to_date('20110231', 'YYYYMMDD') from dual;
select to_date('20110231', 'YYYYMMDD') from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
SQL>
Regards
Thomas
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: to_char() accepting invalid dates?
@ 2011-07-19 14:09 Igor Neyman <ineyman@perceptron.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 9+ messages in thread
From: Igor Neyman @ 2011-07-19 14:09 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
-----Original Message-----
From: Bruce Momjian [mailto:bruce@momjian.us]
Sent: Monday, July 18, 2011 6:02 PM
To: Thomas Kellerer
Cc: pgsql-sql@postgresql.org
Subject: Re: to_char() accepting invalid dates?
Thomas Kellerer wrote:
> Jasen Betts wrote on 18.07.2011 11:23:
> >> postgres=> select to_date('20110231', 'yyyymmdd');
> >>
> >> to_date
> >> ------------
> >> 2011-03-03
> >> (1 row)
> >>
> >> is there a way to have to_date() raise an exception in such a case?
> >
> > it's possible the odd behaviour you get is required by some
standard.
>
> That would be *very* odd indeed.
>
>
> > jasen=# select '20110303'::date;
> Thanks for the tip, this was more a question regarding _why_ to_char()
behaves this way.
> Well, to_char() is based on Oracle's to_char(). How does Oracle
handle
> such a date?
> --
> Bruce Momjian <bruce@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
> + It's impossible for everything to be true. +
This (from Oracle) makes me think, that it's implemented differently:
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> select to_date('20110231', 'yyyymmdd') from dual;
select to_date('20110231', 'yyyymmdd') from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
SQL>
Regards,
Igor Neyman
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: to_char() accepting invalid dates?
@ 2011-07-20 01:03 Bruce Momjian <bruce@momjian.us>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2011-07-20 01:03 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
Thomas Kellerer wrote:
> Bruce Momjian, 19.07.2011 00:02:
> >>>> postgres=> select to_date('20110231', 'yyyymmdd');
> >>>>
> >>>> to_date
> >>>> ------------
> >>>> 2011-03-03
> >>>> (1 row)
> >>>>
> >>>> is there a way to have to_date() raise an exception in such a case?
> >>>
> >>> it's possible the odd behaviour you get is required by some standard.
> >>
> >> That would be *very* odd indeed.
> >>
> >>
> >>> jasen=# select '20110303'::date;
> >> Thanks for the tip, this was more a question regarding _why_ to_char() behaves this way.
> >
> > Well, to_char() is based on Oracle's to_char(). How does Oracle handle
> > such a date?
>
> Oracle throws an error for the above example:
>
> SQL> select to_date('20110231', 'YYYYMMDD') from dual;
> select to_date('20110231', 'YYYYMMDD') from dual
> *
> ERROR at line 1:
> ORA-01839: date not valid for month specified
>
> SQL>
OK, it's a bug then. Let me see if I can find a fix for it.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: to_char() accepting invalid dates?
@ 2011-07-20 07:15 Thomas Kellerer <spam_eater@gmx.net>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 9+ messages in thread
From: Thomas Kellerer @ 2011-07-20 07:15 UTC (permalink / raw)
To: pgsql-sql
Bruce Momjian, 20.07.2011 03:03:
>>> Well, to_char() is based on Oracle's to_char(). How does Oracle handle
>>> such a date?
>>
>> Oracle throws an error for the above example:
>>
>> SQL> select to_date('20110231', 'YYYYMMDD') from dual;
>> select to_date('20110231', 'YYYYMMDD') from dual
>> *
>> ERROR at line 1:
>> ORA-01839: date not valid for month specified
>>
>> SQL>
>
> OK, it's a bug then. Let me see if I can find a fix for it.
Thanks for the info. I didn't know it was modelled after the Oracle implementation.
Regards
Thomas
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: to_char() accepting invalid dates?
@ 2011-07-29 17:48 THOMPSON, JARED (ATTBAPCO) <JT060b@ATT.COM>
0 siblings, 0 replies; 9+ messages in thread
From: THOMPSON, JARED (ATTBAPCO) @ 2011-07-29 17:48 UTC (permalink / raw)
To: spam_eater@gmx.net; +Cc: pgsql-sql
ERROR at line 1:
ORA-01839: date not valid for month specified
That error is coming on
select to_date('20110231', 'YYYYMMDD') from dual;
because there are not 31 days in February 2011, try this:
select to_date('20110228', 'YYYYMMDD') from dual;
Jared Thompson
Internet Operations Group
Office: 678.406.2895
jt060b@att.com <mailto:kyle.dungan@att.com>
This e-mail and any files transmitted with it are AT&T property, are
confidential, and are intended solely for the use of the individual or
entity to whom this email is addressed. If you are not one of the named
recipient(s) or otherwise have reason to believe that you have received
this message in error, please notify the sender and delete this message
immediately from your computer. Any other use, retention, dissemination,
forwarding, printing, or copying of this e-mail is strictly prohibited.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2011-07-29 17:48 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-07-18 08:22 to_char() accepting invalid dates? Thomas Kellerer <spam_eater@gmx.net>
2011-07-18 09:23 ` Jasen Betts <jasen@xnet.co.nz>
2011-07-18 17:07 ` Thomas Kellerer <spam_eater@gmx.net>
2011-07-18 22:02 ` Bruce Momjian <bruce@momjian.us>
2011-07-19 11:30 ` Thomas Kellerer <spam_eater@gmx.net>
2011-07-20 01:03 ` Bruce Momjian <bruce@momjian.us>
2011-07-20 07:15 ` Thomas Kellerer <spam_eater@gmx.net>
2011-07-19 14:09 ` Igor Neyman <ineyman@perceptron.com>
2011-07-29 17:48 Re: to_char() accepting invalid dates? THOMPSON, JARED (ATTBAPCO) <JT060b@ATT.COM>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox