Hi all,

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

Geoff Muldoon schrieb:
Does that help?
http://www.phys.uu.nl/~vgent/calendar/isocalendar.htm

Best regards

Maxim

Maxim Demenko says...
Maxim,

Many thanks, an excellent resource.

"the first calendar week of a year is that one which includes the first
Thursday of that year" was what I needed.

Now to work out my handlers ...

Geoff M

Geoff Muldoon schreef:
Or much simpler: the week with jan 4th is week 1. Not the ISO
definition, but proven to work for the next 4000 years at least

Shakespeare

Shakespeare says...
Yes, I've twigged that this is a neat way of selecting a date that is
always in ISO week 1.

Geoff M