Hi,

only a basic question... I have a stored procedures that return the
week-nr of a day:

----------------------------------------------------------------------
Procedure WEEK_OF_YEAR
INPUT: D DATE
WEEK_NO: INTEGER

DECLARE VARIABLE W INTEGER; /* week number */
DECLARE VARIABLE Y INTEGER; /* year the week belongs to */

BEGIN
W = (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7;
Y = EXTRACT(YEAR FROM D);

IF (W=0) THEN BEGIN
Y = Y - 1;
D = D - EXTRACT(YEARDAY FROM D) - 1; /* last day of previous year;
D is used as temporary variable here */
W = (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7;
END
ELSE
IF (W=53 AND 4>EXTRACT(WEEKDAY FROM (D - EXTRACT(DAY FROM D) + 31)))
THEN BEGIN
Y = Y + 1;
W = 1;
END

/* This is just formatting; you may prefer to make W and Y return
parameters instead. */
WEEK_NO=W;

SUSPEND;
END
----------------------------------------------------------------------

I want to use this SP like a UDF-Function in a select statment to
convert a field value. Example:

Select
WEEK_OF_YEAR(DATEFIELD)
FROM TABLE

but this doesn't work. It returns that WEEK_OF_YEAR is unknown...

Is is possible to use SP in such a way or does I need a Udf-Function?

with best regards from bremen, germany

Nils Bödeker

--
*--------------------------------------------------------*
| Nils Boedeker VERLAG EUGEN ULMER |
| Bürgerwohlsweg 7 Datenbanken und IT-Entwicklung |
| D-28215 Bremen -------------------------------------|
| Germany Tel/Fax: +49 (0) 421 3795-020/-021 |
| Mobilfunk: +49 (0) 172 7468066 |
|--------------------------------------------------------|
| eMail:nboedeker@... | eMail:info@... |
|--------------------------------------------------------|
| http://www.ulmer.de | http://www.nbsoft.de |
|--------------------------------------------------------|
| Available by Yahoo Messenger, yahoo ID: nilsboedeker |
*--------------------------------------------------------*