Re: get first / last date of given week

>Have a look at this simpler non looping version of week_start()
>

That is a nice idea. I had to modify it a bit in order to get
the same answers as my other function ...

CREATE or REPLACE FUNCTION week_start2(integer, integer) RETURNS date AS '
DECLARE
pyear ALIAS FOR $1;
pweek ALIAS FOR $2;

year_start date;
year_start_dow integer;
week_interval interval;
week_date date;
week_year integer;
dow_interval interval;

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

year_start := to_date(pyear, ''yyyy'');
year_start_dow := date_part(''dow'', year_start);
week_interval := pweek-1 || '' week'';
dow_interval := year_start_dow || '' day'';
week_date := year_start - year_start_dow + 1 + week_interval;
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';

_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963