ISO Week of Year

Oracle 10.2.0.3 on Linux ...

I'm having difficulty with a proc which tries to compare data across years at a specific point in time.

For a particular input_date and comparison_year, I'm trying to retrieve a comparison_date based on the day-of-week of the input_date and the corresponding ISO-week-of-year.

EG:
input_date 08-jan-2009 (Thursday, ISO-week-of-year 2) and comparison_year 2007, I'll get a result of 11-jan-2007 (the Thursday of ISO-week-of-year 2 in that year).

I realise that I have now to deal with where there is no matching date, for example where the input_date's year might have 53 ISO-weeks and the comparison_year only 52.

The problem I'm having is working out when ISO-week-of years actually start.

select to_char(to_date('01-jan-1998', 'dd-mon-yyyy'), 'IW') from dual -- this is a Thursday
--

01

select to_char(to_date('01-jan-1999', 'dd-mon-yyyy'), 'IW') from dual -- this is a Friday
--

53

Can anyone help in explaining this?

Geoff M Received on Tue Jan 20 2009 - 17:43:00 CST