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???

----- Original Message -----
From: <rvellacott@...>
To: <ib-support@yahoogroups.com>
Sent: Friday, November 23, 2001 6:01 PM
Subject: [ib-support] Re: SP to extract a week number


> 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
> >
> >
> >
>
>
>
> 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/
>
>