Re: ORA-01820 help

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

If you want to calculate a date from a week number for a certain year, (in ISO weeks) take jan 4 of that year (to_date('04-01-'||<year>)), add weekno* 7 to it and you get a date in the week you are looking for, and it will be the same day (mon, tue etc) as jan 4. (Though rules of calculating ISO week 1 may look complex, jan 4 is ALWAYS in week 1). If you want the first day of the week trunc the date it with 'ww'. In my system it's the monday of that week.

Maybe it helps....

Shakespeare Received on Tue Sep 11 2007 - 08:00:41 CDT