Re: ORA-01820 help

In article <bojde3hf65rvt8rvntboga3kpi9hga142t_at_4ax.com>, says...
> <geoff.muldoon_at_trap.gmail.com> wrote:

> >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.
>
> Here's a start:
>
> SELECT
> SysDate
> + (
> TO_NUMBER(TO_CHAR(SySDate, 'd'))
> - TO_NUMBER(TO_CHAR(SysDate -
> TO_YMINTERVAL('1-0'), 'd'))
> )
> + (7 * (TO_NUMBER(TO_CHAR(SysDate, 'ww'))
> - TO_NUMBER(TO_CHAR(SysDate -
> TO_YMINTERVAL('1-0'), 'ww'))))
> - TO_YMINTERVAL('1-0')
> FROM
> Dual;

With the example that I gave, the result of this is out by 1 week (gives me 13-JAN-2000).

Thanks though, might give me some ammo.

Geoff M Received on Tue Sep 11 2007 - 19:16:28 CDT