Re: Calculating the Week of the Year when "Stat Week" runs Sunday through Saturday.

Tim Frawley wrote:
> I am attempting to calculate a "Stat Week" (week of year) based on a
> date value. Unfortunately our "Stat Weeks" run from Sunday to
> Saturday. These are the rules as they have been explained to me:
>
> 1. The ADF&G Statistical Week is 7 days long and runs from
> Sunday through Saturday.
> 2. The first Statistical week of the year may be less than
> 7 days and ends on the first Saturday.
> 3. Subsequent weeks are numbered chronologically through
> the end of the year.
>
> This renders the rather simple function to_char(datevalue, 'WW')
> inaccurate in for our situation.
>
> Other than looping every single day of the year to get to the value
> needed, does anyone have an easier/faster/simpler solution?

CREATE OR REPLACE FUNCTION stat_week(in_date DATE) IS BEGIN
  RETURN CEIL(TO_NUMBER(SUBSTR(TO_CHAR(NEXT_DAY(in_date-1,'SAT'),

         'DDD YYYY'),1,3)/7);
EXCEPTION
  WHEN OTHERS THEN RAISE;
END;
/

The NEXT_DAY function finds the first Saturday following the day before the input date. I use in_date-1 because if in_date is a Saturday the NEXT_DAY function will return the Saturday a week later and that's not what we want. Next the TO_CHAR function converts this date into DDD (day of year) YYYY format. The SUBSTR function then extracts the first three characters which are the day of the year. Next we convert that to a number and divide by 7. Finally the CEIL function rounds this up to the next integer.

Voila! the stat date.

Ken Denny Received on Wed Feb 09 2005 - 13:36:12 CST