Re: ORA-01820 help

On Wed, 12 Sep 2007 10:16:28 +1000, Geoff Muldoon <geoff.muldoon_at_trap.gmail.com> wrote:

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

Correct. For ease in the example i used the standard "week of year" calculation. Your request, however, is more personalized and can be handled by adding/subtracting another week based on whether the first "day of the year"'s "day of week" is before or after the calulated day's "day of week").

B.

>Thanks though, might give me some ammo.
>
>Geoff M
Received on Wed Sep 12 2007 - 07:51:15 CDT