My apologies. That wasn't quite right either. This, I am pretty
sure, works for all dates and all years.


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
DAY_OF_WEEK = EXTRACT(WEEKDAY FROM D);
IF (DAY_OF_WEEK = 0) THEN DAY_OF_WEEK = 7;
D = D + 7 - DAY_OF_WEEK;
YEARNUM = EXTRACT (YEAR FROM D);
WK1BEGINS = CAST('01/04/'||CAST(YEARNUM AS CHAR(4)) AS DATE);
DAY_OF_WEEK = EXTRACT(WEEKDAY FROM WK1BEGINS);
IF (DAY_OF_WEEK = 0) 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;
SUSPEND;
END

--- In ib-support@y..., rvellacott@p... wrote:
> 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
>
>
>