Re: [GENERAL] to_timestamp() and quarters

A. Kretschmer *EXTERN*
> > Well, I can easily make it do what you expect, and I don't see many
> > error returns in that area of the code, so I just wrote a patch that
> > does what you would expect rather than throw an error.
> 
> Well, that's great and better than an error, thx.
> 
> > 	test=> select to_date('2010-7', 'YYYY-Q');
> > 	  to_date
> > 	------------
> > 	 2011-07-04
> > 	(1 row)
> 
> Is this per SQL-Spec? I would expect an error for a quarter not in
> (1,2,3,4).
> 
> But stop, now i see:
> 
> test=*# select to_date('2010-02-29', 'YYYY-MM-DD');
>   to_date
> ------------
>  2010-03-01
> (1 row)
> 
> So it is maybe a congruously behavior ;-)

Ugh. I thought that to_date was an Oracle compatibility function.

SQL> select to_date('2010-02-29', 'YYYY-MM-DD') from dual;
select to_date('2010-02-29', 'YYYY-MM-DD') from dual
               *
ERROR at line 1:
ORA-01839: date not valid for month specified

And for that matter:

SQL> select to_date('2010-7', 'YYYY-Q') from dual;
select to_date('2010-7', 'YYYY-Q') from dual
                         *
ERROR at line 1:
ORA-01820: format code cannot appear in date input format

Oracle allows Q only when converting date to string.
So this can be seen as an extension.

But allowing 2010-02-29 is incompatible and smacks of MySQL...

Yours,
Laurenz Albe