Thread: to_char() accepting invalid dates?

to_char() accepting invalid dates?

From

Thomas Kellerer

Date:

18 July 2011, 08:22:49

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

Re: to_char() accepting invalid dates?

From

Jasen Betts

Date:

18 July 2011, 09:30:34

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

Re: to_char() accepting invalid dates?

From

Thomas Kellerer

Date:

18 July 2011, 17:08:00

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

Re: to_char() accepting invalid dates?

From

Bruce Momjian

Date:

18 July 2011, 22:02:34

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. +

Re: to_char() accepting invalid dates?

From

Thomas Kellerer

Date:

19 July 2011, 11:30:41

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

Re: to_char() accepting invalid dates?

From

"Igor Neyman"

Date:

19 July 2011, 14:21:26


-----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

Re: to_char() accepting invalid dates?

From

Bruce Momjian

Date:

20 July 2011, 01:03:50

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. +

Re: to_char() accepting invalid dates?

From

Thomas Kellerer

Date:

20 July 2011, 07:15:13

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

compile postgres with visual studio 2010

From

"Sofer, Yuval"

Date:

20 July 2011, 11:29:30

Hi 

I would like to build Postgres from source with the visual studio 2010 compiler

Is it supported? Is there any document which describes the process of the implementation? 

Thanks, 


Yuval Sofer
BMC Software
CTM&D Business Unit
DBA Team
972-52-4286-282
yuval_sofer@bmc.com

Re: compile postgres with visual studio 2010

From

Kevin Crain

Date:

20 July 2011, 14:40:21

The documentation only mentions Visual Studio 2005 and Visual Studio
2008, but I see no reason why it shouldn't work.  Check out the
requirements listed in the documentation:
http://www.postgresql.org/docs/9.0/interactive/install-windows-full.html

On Wed, Jul 20, 2011 at 3:55 AM, Sofer, Yuval <Yuval_Sofer@bmc.com> wrote:
> Hi
>
> I would like to build Postgres from source with the visual studio 2010 compiler
>
> Is it supported? Is there any document which describes the process of the implementation?
>
> Thanks,
>
>
> Yuval Sofer
> BMC Software
> CTM&D Business Unit
> DBA Team
> 972-52-4286-282
> yuval_sofer@bmc.com
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: to_char() accepting invalid dates?

From

"THOMPSON, JARED (ATTBAPCO)"

Date:

02 August 2011, 14:37:20

<div class="WordSection1"><p class="MsoNormal" style="tab-stops:45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt
366.4pt412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt"><span
style="font-size:10.0pt;font-family:"CourierNew";mso-fareast-font-family:"Times New Roman";color:black">ERROR at line
1:</span><pclass="MsoNormal" style="tab-stops:45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt
458.0pt503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt"><span style="font-size:10.0pt;font-family:"Courier
New";mso-fareast-font-family:"TimesNew Roman";color:black">ORA-01839: date not valid for month specified</span><p
class="MsoNormal"> <pclass="MsoNormal"> <p class="MsoNormal">That error is coming on <pre><span
style="color:black">selectto_date('20110231', 'YYYYMMDD') from dual;</span></pre><p class="MsoNormal"> <p
class="MsoNormal">becausethere are not 31 days in February 2011, try this:<p class="MsoNormal"> <p
class="MsoNormal">selectto_date('20110228', 'YYYYMMDD') from dual;<p class="MsoNormal"> <p class="MsoNormal"><b
style="mso-bidi-font-weight:normal"><spanstyle="font-size:12.0pt;font-family:"Bookman Old
Style","serif";mso-fareast-font-family:"TimesNew Roman";mso-no-proof:yes">Jared Thompson</span></b><p
class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:"Palatino Linotype","serif";mso-fareast-font-family:"Times
NewRoman";mso-no-proof:yes">Internet Operations Group</span><span style="font-family:"Palatino
Linotype","serif";mso-fareast-font-family:"TimesNew Roman";mso-no-proof:yes"></span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"PalatinoLinotype","serif";mso-fareast-font-family:"Times New
Roman";mso-no-proof:yes">Office:678.406.2895</span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"PalatinoLinotype","serif";mso-fareast-font-family:"Times New
Roman";mso-no-proof:yes"><ahref="mailto:kyle.dungan@att.com"><span
style="mso-bidi-font-size:11.0pt;color:blue">jt060b@att.com</span></a></span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:"PalatinoLinotype","serif";mso-fareast-font-family:"Times New
Roman";mso-no-proof:yes"> </span><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:"Palatino
Linotype","serif";mso-fareast-font-family:"TimesNew Roman";mso-no-proof:yes"> </span><p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><i><spanstyle="font-size:10.0pt;font-family:"Times New
Roman","serif";mso-fareast-font-family:"TimesNew Roman";mso-bidi-font-family:"Times New
Roman";color:#1F497D;mso-no-proof:yes">Thise-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
arenot one of the named recipient(s) or otherwise have reason to believe that you have received this message in error,
pleasenotify 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.</span></i><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif";mso-fareast-font-family:"Times New
Roman";mso-bidi-font-family:"TimesNew Roman";mso-no-proof:yes"></span><p class="MsoNormal"> </div>