Re: get first / last date of given week - Mailing list pgsql-general

>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