ORA-01820 help

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 Received on Mon Sep 10 2007 - 23:53:28 CDT