Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 4EC484767EE
	for <pgsql-sql@postgresql.org>; Wed, 25 Sep 2002 10:49:35 -0400 (EDT)
Received: from news.hub.org (news.hub.org [64.49.215.80])
	by postgresql.org (Postfix) with ESMTP id ED8B947679E
	for <pgsql-sql@postgresql.org>; Wed, 25 Sep 2002 10:49:32 -0400 (EDT)
Received: by news.hub.org (Postfix, from userid 8)
	id 022063817D0; Wed, 25 Sep 2002 10:49:36 -0400 (EDT)
From: Dmitry Tkach <dmitry@openratings.com>
X-Newsgroups: comp.databases.postgresql.sql
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
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 62
Message-ID: <3D91CAB0.7080003@openratings.com>
References: <1032672769.21707.62.camel@ciderbox>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: news@hub.org
To: David Stanaway <david@stanaway.net>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.7+) Gecko/20020123
X-Accept-Language: en-us
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/315
X-Sequence-Number: 9592

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';
> 
>