Get a week of year in Firebird PSQL

2008 is now a busy year for me hehehe, long time not post an article again, mm... I want to share some technique in FB to get a week of year (I use it for my last project), I read a nice article from this site and see that in FB tracker there is a discussion about this stuff. The simple PSQL technique that I had learn is:

(EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7

It can give us the week of year, but I also need the start and the end of that week, so I must think about this and I found a solution, here I capture some code from my project to know the start date and end date of that week of year

rangetglawal = cast((extract(year from D)) || '.01.' || '01' as date);
rangetglawal = rangetglawal - extract(weekday from rangetglawal);
week = (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM (D-1))+7)/7 +1;

viStartWeek = ((EXTRACT(YEARDAY FROM rangetglawal) - EXTRACT(WEEKDAY FROM (rangetglawal-1))+7)/7+1);

if (extract(year from D)=cast(extract(year from rangetglawal)as varchar(4))) then
begin
"viTempWeek" = week - "viStartWeek"-1;
rangetglawal = rangetglawal + ("viTempWeek"*7)+1;
end else
begin
rangetglawal=rangetglawal+((week-1)*7)+1;
end
rangetglakhir=rangetglawal+6;

The start date of that week will be in "rangetglawal" variable and the end of that week will be in "rangetglakhir" variable. I add plus 1 in the original code for get a week of year because the original code will result 0 for first week, and I want it to start from 1.

Enjoy this code and perhaps it can be usefull.