Re: Queries Using Week

[Nathan Treadway <nathant@ONTKO.COM>]

> Hi, I am using SQR3 and Oracle 7.2 and have written a report the uses a
> Oracle edit mask: to_char(changedate, 'WW'). The returns the week of the
> year that change date is in. There is just one small problem. Oracle assumes
> that since Jan 1, 1997 was a Wednesday, the first day of the week is a
> Wednesday and the last day is a Tuesday. I need the first day of the week to
> be a Monday and the last day to be a Sunday. Now I've used an Alter session

I'm not sure¬ but I think the "IW" date mask (rather than WW) will do what
you want.  It's defined as "Week of year (1-52 or 1-53) based on the ISO
standard."  I'm not sure what the ISO standard is here, but a quick test
seemed to show that it does what you want.
  
If not, here is another possibility. In Oracle if you add an integer N to a
date, the result is a date N days later than the orginal date. Thus, if you
want Week 01 to be Mon, Dec 30, 1996 to Sun, Jan 5, 1997, you could use
to_char(changedate + 2, 'WW') to "shift" the week backwards in time by two
days.

To calculate the "2" used here, you would take the day of the week of Jan 1
and subtract the day of the week you want to be the "beginning". Oracle (by
default -- one may change this using "alter session") counts day of the week
starting with Sunday = 1.

Thus, if you put early on in your program:

begin-select
to_char(to_date('1-JAN-' || $year , 'dd-mon-yyyy'),'D') - 2 &week_start_offset
from dual
end-select

(assuming you know beforehand what year you care about, and that you want
Monday, day 2, to be the start of the week), then you can use

to_char(changedate + &week_start_offset, 'WW') 

to do what you want.  (Jan 1, 1997 is a Wednesday, which is day 4, and "4 - 2"
would give &week_start_offset the "2" used in the original example.)

                                                        Nathan

----------------------------------------------------------------------------
Nathan Treadway    | Ray Ontko & Co. | info@ontko.com (auto-reply server)
nathant@ontko.com  | Richmond, IN    | ftp.ontko.com, http://www.ontko.com/