Subject: Re: [SQL] Start and End Day of a Week
From: Luis =?ISO-8859-1?Q?Maga=F1a?= <joe666@gnovus.com>
Reply-To: joe666@gnovus.com
To: Postgresql General Mail List <pgsql-general@postgresql.org>,
	Postgresql SQL Mail List <pgsql-sql@postgresql.org>
In-Reply-To: <1044651478.1124.3.camel@kerberus>
References: <1044651478.1124.3.camel@kerberus>
Content-Type: text/plain; charset=ISO-8859-1
Organization: Gnovus Networks & Software
Message-Id: <1044661959.1123.8.camel@kerberus>
Mime-Version: 1.0
Date: 07 Feb 2003 17:52:39 -0600
Content-Transfer-Encoding: quoted-printable

Hi,=20

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=20
	(SELECT  CAST(year || ''-01-01'' AS TIMESTAMP) -=20
                      (date_part(''dow'',CAST(year || ''-01-01'' AS
TIMESTAMP))||'' days'')::INTERVAL) +=20
                      ((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) -=20
			(date_part(''dow'',CAST(year || ''-01-01'' AS TIMESTAMP))||''
days'')::INTERVAL) +=20
			((week*7)||'' days'')::INTERVAL - ''1 days''::INTERVAL;

RETURN endsin;

END;
' LANGUAGE 'plpgsql';

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