Re: Queries Using Week

[Ray Ontko <rayo@ONTKO.COM>]

Charlie,

> 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
> command in a SQL to change Oracle's date format for a report, is there a way
> to change the day a week starts? I know this is kind of a Oracle question so
> I hope I don't get flamed to death, but I'm trying to write this SQR3 report
> using this function or a reasonable equivalent.

I struggled with this once before, and it ended up being a
complicated nesting of date functions and conversions and
adds, but I think you can get the day of week number of Jan 1, as
well, and subtract this number (of days) from the date before you check
the week.  If you believe that the first week is actually the first
full week, you may need to add 7 days to date before converting.

Make sense?  Play around with a few sample dates and you
should be able to piece something together that will work
no matter what day the year starts in.  Beware, not all years
will have the same number of weeks (if I remember correctly).

Ray
----------------------------------------------------------------------
Ray Ontko       |  Ray Ontko & Co  |  "Time for a new signature line."
rayo@ontko.com  |  Richmond, In    |  See us at http://www.ontko.com/