From: Luis Magaña <joe666@gnovus.com>
To: Postgresql General Mail List <pgsql-general@postgresql.org>
To: Postgresql SQL Mail List <pgsql-sql@postgresql.org>
Subject: Re: [SQL] Start and End Day of a Week
Date: 07 Feb 2003 17:52:39 -0600
Message-ID: <1044661959.1123.8.camel@kerberus> (raw)
In-Reply-To: <1044651478.1124.3.camel@kerberus>
References: <1044651478.1124.3.camel@kerberus>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: joe666@gnovus.com, pgsql-general@postgresql.org
Subject: Re: [SQL] Start and End Day of a Week
In-Reply-To: <1044661959.1123.8.camel@kerberus>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox