Re: [SQL] Start and End Day of a Week

Hi,

I've found my own answer... this is in case somebody needs it someday,
it seems that works properly in PG 7.3, one function gives the start of
the week and the other one the end:

CREATE OR REPLACE FUNCTION weekstart(int4,int4)
RETURNS TIMESTAMP
AS'
DECLARE
    year ALIAS FOR $1;
    week ALIAS FOR $2;
    startsin TIMESTAMP;
BEGIN

SELECT INTO startsin
    (SELECT  CAST(year || ''-01-01'' AS TIMESTAMP) -
                      (date_part(''dow'',CAST(year || ''-01-01'' AS
TIMESTAMP))||'' days'')::INTERVAL) +
                      ((week*7)||'' days'')::INTERVAL - ''7
days''::INTERVAL;

RETURN startsin;

END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION weekend(int4,int4)
RETURNS TIMESTAMP
AS'
DECLARE
    year ALIAS FOR $1;
    week ALIAS FOR $2;
    endsin TIMESTAMP;
BEGIN

SELECT INTO endsin
     (SELECT  CAST(year || ''-01-01'' AS TIMESTAMP) -
            (date_part(''dow'',CAST(year || ''-01-01'' AS TIMESTAMP))||''
days'')::INTERVAL) +
            ((week*7)||'' days'')::INTERVAL - ''1 days''::INTERVAL;

RETURN endsin;

END;
' LANGUAGE 'plpgsql';

On Fri, 2003-02-07 at 14:57, Luis Magaña wrote:
> Hi,
>
> how can I get the first and last day of a week given the year and the
> week number ???
>
> example
>
> week 3 of 2003 starts in Jan 12th and ends on Jan 18th.
>
> how to know the last two values given the year and the number of week ?
>
> thanks.
>
> Best Regards.
--
Luis Magaña.
Gnovus Networks & Software.
www.gnovus.com