--- In firebird-support@yahoogroups.com, Richard Wesley
<hawkfish@...> wrote:

>
>
> On May 21, 2006, at 23:25, Adam wrote:
>
> > select extract(weekday from current_date)+1 from RDB$DATABASE;
> >
> > (the +1 is just so it returns the same as Delphi 1-7 instead of 0-

6)

>
> Don't forget to check for NULLs with real data:
>
> (CASE WHEN <expr> IS NULL THEN CAST(NULL AS SMALLINT) ELSE 1 +
> EXTRACT(WEEKDAY FROM <expr> ) END)

Thanks for the reminder. I neglected to consider NULL. My only excuse
is that in my requirement, I am dealing with a not NULL field. In any
case, EXTRACT returns NULL if the field is NULL, so that logic is not
required. NULL + 1 is also NULL.

---

CREATE OR ALTER PROCEDURE SP_DAYOFWEEK
(
SOMEDATE TIMESTAMP
)
RETURNS
(
DOW INTEGER
)
AS
BEGIN
DOW = EXTRACT(WEEKDAY FROM :SOMEDATE) + 1;
SUSPEND;
END
^

works as hoped even for NULL fields.

SELECT DOW
FROM SP_DAYOFWEEK('TODAY');

SELECT DOW
FROM SP_DAYOFWEEK(NULL);


Adam