Here is another attempt.

ALTER PROCEDURE GET_WEEK
(D DATE )
RETURNS
(WEEK_NO VARCHAR(8))
AS
declare variable WK1BEGINS DATE;
declare variable YEARNUM INTEGER;
declare variable WEEKSTR CHAR(2);
declare variable WEEKNUM INTEGER;
declare variable DAY_OF_WEEK INTEGER;
BEGIN
/* switch to the Sunday of the week to avoid this year week1 dates
*/
/* which are dated in the previous year */
D = D + 7 - EXTRACT(WEEKDAY FROM D);
YEARNUM = EXTRACT (YEAR FROM D);
WK1BEGINS = CAST('01/04/'||CAST(YEARNUM AS CHAR(4)) AS DATE);
DAY_OF_WEEK = EXTRACT(WEEKDAY FROM WK1BEGINS) + 1;
IF (DAY_OF_WEEK = 1) THEN DAY_OF_WEEK = 7;
WK1BEGINS = WK1BEGINS + 1 - DAY_OF_WEEK;
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

--- In ib-support@y..., Jörg Schiemann <schimmi@s...> wrote:
> 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@p...>
> To: <ib-support@y...>
> 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/
> >
> >
> >
> >