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