You forgot to check for the last days of the year. They may already belong
to the first week for the next year.

/Jörg

----- Original Message -----
From: <rvellacott@...>
To: <ib-support@yahoogroups.com>
Sent: Wednesday, November 14, 2001 4:49 PM
Subject: [ib-support] Re: SP to extract a week number


> Many thanks for this prompting. It is ISO 8601, which specifies that
> day 1 is a Monday, and Week 1 is the week in which 4th January
> falls. This is the same as saying that it is the week in which the
> first Thursday in January falls.
>
> Here is a revised SP to get the correct week number.
>
> CREATE PROCEDURE GET_WEEK
> (
> D TIMESTAMP
> )
> RETURNS
> (
> WEEK_NO VARCHAR(8)
> )
> AS
> declare variable DAYNUM integer;
> declare variable WK1BEGINS DATE;
> declare variable YEARNUM integer;
> declare variable YEAR_BEGINS DATE;
> declare variable WEEKSTR CHAR(2);
> declare variable WEEKNUM integer;
> declare variable WEEK1_FOUND char(1);
> BEGIN
> YEARNUM = EXTRACT (YEAR FROM D);
> WEEK1_FOUND = 'F';
> WHILE (WEEK1_FOUND <> 'T') DO
> BEGIN
> /* do this twice, in case D falls in the current year, but */
> /* before the beginning of week 1. */
> WK1BEGINS = CAST('01/04/'||CAST(YEARNUM AS CHAR(4)) AS DATE);
> WK1BEGINS = WK1BEGINS + 1 - EXTRACT(WEEKDAY FROM WK1BEGINS);
> IF (D < WK1BEGINS) THEN
> YEARNUM = YEARNUM -1;
> ELSE
> WEEK1_FOUND = 'T';
> END
> WEEKNUM = CAST(((D-WK1BEGINS)/7)+0.5 AS INTEGER);
> IF (WEEKNUM < 10) THEN
> WEEKSTR = '0'||CAST(WEEKNUM AS CHAR(1));
> ELSE
> WEEKSTR = CAST(WEEKNUM AS CHAR(2));
> WEEK_NO = CAST(YEARNUM AS CHAR(4))||'/'||WEEKSTR;
> END
>
>
>
>
> > I believe I heard somewhere that the ISO standard for numbering
> weeks of
> > the year calls for week 1 to be the first week of the year which
> contains a
> > Thursday. I'm not 100% sure of the definition but I know there is
> one so
> > you might want to adjust your procedure to conform to it. That way
> it will
> > hopefully be useful to a wider audience.
>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>
>