[SQL] to_char() accepting invalid dates?

2011-07-18 Thread Thomas Kellerer 

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', 'mmdd');
  to_date

 2011-03-03
(1 row)

is there a way to have to_date() raise an exception in such a case?

Regards
Thomas


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

Re: [SQL] to_char() accepting invalid dates?

2011-07-18 Thread Jasen Betts 
On 2011-07-18, Thomas Kellerer  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', 'mmdd');
>
>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 'mmdd' 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 -MM-DD and possilby a locale 
dependant form with the year last 

-- 
⚂⚃ 100% natural


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

Re: [SQL] to_char() accepting invalid dates?

2011-07-18 Thread Thomas Kellerer 

Jasen Betts wrote on 18.07.2011 11:23:

postgres=>  select to_date('20110231', 'mmdd');

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


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

Re: [SQL] to_char() accepting invalid dates?

2011-07-18 Thread Bruce Momjian 
Thomas Kellerer wrote:
> Jasen Betts wrote on 18.07.2011 11:23:
> >> postgres=>  select to_date('20110231', 'mmdd');
> >>
> >> 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  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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