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.