Re: ORA-01820 help

"Geoff Muldoon" <geoff.muldoon_at_trap.gmail.com> schreef in bericht news:MPG.2150ca5755b8536c9899ca_at_news.readfreenews.net...
> Hi all,
>
> I'm trying to use a combo of to_char and to_date to calculate from a
> provided date, the date of the corresponding day-of-week of the
> corresponding week-of-year of the previous year.
>
> For example, 11-JAN-2001 (Thurs, wk 2 of 2001) should give me 06-JAN-2000
> (Thurs, wk 2 of 2000), noting that there was no Thurs in week 1 that year.
>
> select
> to_char(sysdate, 'd-')||to_char(sysdate, 'ww-')||(to_char(sysdate, 'yyyy')
> - 1) from dual;
> gives me 2-37-2006 as expected.
>
> select
> to_date(to_char(sysdate, 'd-')||to_char(sysdate, 'ww-')||(to_char(sysdate,
> 'yyyy') - 1), 'd-ww-yyyy')
> from dual;
> gives me the ORA-01820 error message.
>
> Already tried using IW format instead of WW format, etc.
>
> Any clues on how this can be achieved?
>
> Oracle 10.2.0.3 on linux FWIW.
>
> Geoff M

Simplifying your statement to
select to_date('1-37-2007', 'd-ww-yyyy') from dual causes the same ) ORA-01820...

Looks like ww is not allowed in to_date...

Shakespeare Received on Tue Sep 11 2007 - 06:28:37 CDT