Re: ORA-01820 help

Shakespeare says...

> >> "Geoff Muldoon" <geoff.muldoon_at_trap.gmail.com> schreef in bericht

> >>> 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.

> > 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....)

Maybe in postgresql, where it appears to be valid.

> 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....

Thanks heaps.

trunc with 'd', not with 'ww' (which gives same d-o-w as first d-o-y).

select
trunc(to_date('04-JAN-'||(to_char(v_date, 'yyyy') - 1), 'dd-mon-yyyy') + ((to_char(v_date, 'iw') - 1) * 7), 'd') + to_char(v_date, 'd')
- 1
from dual;

seems to test OK so far.

Geoff M Received on Tue Sep 11 2007 - 19:57:01 CDT