Re: ORA-01820 help

On Tue, 11 Sep 2007 14:53:28 +1000, Geoff Muldoon <geoff.muldoon_at_trap.gmail.com> wrote:

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

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;

B.

>
>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
Received on Tue Sep 11 2007 - 12:48:03 CDT