From: Dmitry Tkach <dmitry@openratings.com>
To: David Stanaway <david@stanaway.net>
Subject: Re: Is there a better way than this to get the start and end of a month?
Date: Wed, 25 Sep 2002 10:39:44 -0400
Message-ID: <3D91CAB0.7080003@openratings.com> (raw)
References: <1032672769.21707.62.camel@ciderbox>
Sorry, the previous message was wrong...
This is better:
create function month_start (date) as
'select date_trunc ('month', $1)::date;'
language 'sql';
create function month_end (date) as
'select month_start ($1) - 1 + interval '1 month';
language 'sql';
I hope, it helps...
Dima
David Stanaway wrote:
> Here are the 2 functions I have at the moment. I was wondering if
> someone had a better way?
>
> CREATE OR REPLACE FUNCTION month_start (date)
> RETURNS date
> AS '
> DECLARE
> day ALIAS FOR $1;
> BEGIN
> RETURN day - (extract(''day'' FROM day)||'' days'')::interval +
> ''1 day''::interval;
> END;
> '
> LANGUAGE 'plpgsql';
>
> CREATE OR REPLACE FUNCTION month_end (date)
> RETURNS date
> AS '
> DECLARE
> day ALIAS FOR $1;
> month int;
> year int;
> BEGIN
> month := extract(''month'' FROM day);
> year := extract(''year'' FROM day);
> IF month = 12 THEN
> month := 1;
> year := year +1;
> ELSE
> month := month +1;
> END IF;
> RETURN (''01-''||month||''-''||year)::date -
> ''1 day''::interval;
> END;
> '
> LANGUAGE 'plpgsql';
>
>
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: dmitry@openratings.com, david@stanaway.net
Subject: Re: Is there a better way than this to get the start and end of a month?
In-Reply-To: <3D91CAB0.7080003@openratings.com>
* 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