--- In firebird-support@yahoogroups.com, "lacakus" <lacak@...> wrote:

>
> Hi,
> I would like ask if there is any way in Firebird extract week number
> from
> date (so number between 1 and 52(53?)) ?
> Syntax like
> extract(week from d)
> If there is now no such feature, is it planned ?
> Should I post this as feature request to firebird tracker ?
> Thanks
> -Laco
>

Hello,

how about using a selectable stored procedure like this:

create procedure sp_weekofyear (
adate date
) returns (
weekofyear integer
) as
declare variable i integer;
declare variable y integer;
begin
weekofyear = null;
if (not :adate is null) then
begin
/* 1st thursday of week... */
while (extract(weekday from :adate) <> 1) do
adate = :adate - 1;
adate = :adate + 3;
/* 1st thursday of year... */
i = 1;
y = extract(year from :adate);
while (extract(weekday from cast('0' || :i || '.01.' || :y as date)) <> 4) do
i = :i + 1;
i = :i - 1;
/* calc result... */
weekofyear = 1 + (extract(yearday from :adate) - :i) / 7;
suspend;
end
end


...in a select statement like this:


select
sp_weekofyear.weekofyear,
sum(sometable.somevalue)
from ben
left join sp_weekofyear (sometable.logindatum) on 1 = 1
group by sp_weekofyear.weekofyear


Greetings
Thomas