Re: get first / last date of given week

Lee,

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

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

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

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

Lee Harr wrote:

> 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
>