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

2011-07-19 Thread Thomas Kellerer 

Bruce Momjian, 19.07.2011 00:02:

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?


Oracle throws an error for the above example:

SQL> select to_date('20110231', 'MMDD') from dual;
select to_date('20110231', 'MMDD') from dual
   *
ERROR at line 1:
ORA-01839: date not valid for month specified

SQL>


Regards
Thomas

 




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

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

2011-07-19 Thread Igor Neyman 


-Original Message-
From: Bruce Momjian [mailto:[email protected]] 
Sent: Monday, July 18, 2011 6:02 PM
To: Thomas Kellerer
Cc: [email protected]
Subject: Re: to_char() accepting invalid dates?

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


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', 'mmdd') from dual;
select to_date('20110231', 'mmdd') from dual
   *
ERROR at line 1:
ORA-01839: date not valid for month specified


SQL>


Regards,
Igor Neyman

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

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

2011-07-19 Thread Bruce Momjian 
Thomas Kellerer wrote:
> Bruce Momjian, 19.07.2011 00:02:
>  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?
> 
> Oracle throws an error for the above example:
> 
> SQL> select to_date('20110231', 'MMDD') from dual;
> select to_date('20110231', 'MMDD') 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  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

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