Re: ORA-01820 help

"Shakespeare" <whatsin_at_xs4all.nl> schreef in bericht news:46e67c02$0$238$e4fe514c_at_news.xs4all.nl...
>
> "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
>

Take a look at
http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10759/sql_elements004.htm#g996827

and look at the column "Specify in TO_* datetime functions?" in table 2-16. It's N(O) for WW which means you can not use it in TO_DATE functions (strange enough, I have seen examples of to_date(...ww) somewhere....)

Shakespeare Received on Tue Sep 11 2007 - 07:45:17 CDT