Using FB, passing 'now' as a parameter seems to fail intermittently,
maybe because the parameter was defined as DATE, and 'now' is of type
TIMESTAMP.

Here is yet another version. Previous version did not like dates
within the current year, but falling prior to the start of week 1.


ALTER PROCEDURE GET_WEEK( D TIMESTAMP )
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 = D + 7;
WHILE (WK1BEGINS > D) DO
BEGIN
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;
IF (WK1BEGINS > D) THEN
YEARNUM = YEARNUM - 1;
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;
SUSPEND;
END

--- In ib-support@y..., "ibrahim Bulut" <ibrahim@b...> wrote:
> This procedure is not workin.
> I am using Interbase 6.
> Client Dialect 3
>
> I execute this sql statement
> Select * from Get_Week('Now');
>
> And I get this error message
> Unknown error
> Statement: select * from get_week('Now')
>
> After this message I get the result set
>
> Same problem is occured when I use While..Do loop in this stored
procedure
>
>
> What is the problem???
>
>
>
>