get first / last date of given week

[Lee Harr]

I wrote a function to return the first date of a given week
(and a few related functions) :
-- return the first date in the given week
CREATE or REPLACE FUNCTION week_start(integer, integer) RETURNS date AS '
DECLARE
pyear ALIAS FOR $1;
pweek ALIAS FOR $2;

year_text text;
year_start date;

week_text text;
interval_text text;
week_interval interval;
week_date date;
week_year integer;

day_interval interval := ''1 day'';

wk integer;

BEGIN
IF pweek < 1 THEN
RAISE EXCEPTION ''No negative week numbers'';
END IF;
IF pweek > 53 THEN
RAISE EXCEPTION ''No week numbers over 53'';
END IF;

year_text := pyear-1 || ''-12-15'';
year_start := year_text::date;

interval_text := pweek || '' week'';
week_interval := interval_text::interval;
week_date := year_start + week_interval;

wk := extract(week FROM week_date);
WHILE wk <> pweek LOOP
week_date := week_date + day_interval;
wk := extract(week FROM week_date);
END LOOP;

week_year := extract(year FROM week_date);
IF week_year > pyear THEN
RAISE EXCEPTION ''No week 53 in this year'';
END IF;

RETURN week_date;
END;
' LANGUAGE 'plpgsql';
-- return the first date in this current week
CREATE or REPLACE FUNCTION week_start() RETURNS date AS '
DECLARE
yr integer;
wk integer;

BEGIN
yr := extract(year from current_date);
wk := extract(week from current_date);

RETURN week_start(yr, wk);

END;
' LANGUAGE 'plpgsql';
-- return the last date in the given year/week
CREATE or REPLACE FUNCTION week_end(integer, integer) RETURNS date AS '
DECLARE
pyear ALIAS FOR $1;
pweek ALIAS FOR $2;

BEGIN
RETURN week_start(pyear, pweek) + interval ''6 days'';

END;
' LANGUAGE 'plpgsql';
-- return the last date in the current week
CREATE or REPLACE FUNCTION week_end() RETURNS date AS '
DECLARE
yr integer;
wk integer;

BEGIN
yr := extract(year from current_date);
wk := extract(week from current_date);

RETURN week_end(yr, wk);

END;
' LANGUAGE 'plpgsql';

Have a reinvented a wheel here? (badly? ;o)
Is there a cookbook where I should post this code?

__________________________________________________ _______________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org