Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id AC320476116
	for <pgsql-sql@postgresql.org>; Sun, 22 Sep 2002 01:34:56 -0400 (EDT)
Received: from ciderbox.stanaway.net (ciderbox.stanaway.net [170.76.50.98])
	by postgresql.org (Postfix) with ESMTP id 6341E475C8B
	for <pgsql-sql@postgresql.org>; Sun, 22 Sep 2002 01:34:26 -0400 (EDT)
Received: by ciderbox.stanaway.net (Postfix, from userid 1000)
	id 039C192B00; Sun, 22 Sep 2002 00:32:49 -0500 (CDT)
Subject: Is there a better way than this to get the start and end of a month?
From: David Stanaway <david@stanaway.net>
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-Mailer: Ximian Evolution 1.0.8 
Date: 22 Sep 2002 00:32:49 -0500
Message-Id: <1032672769.21707.62.camel@ciderbox>
Mime-Version: 1.0
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/257
X-Sequence-Number: 9534

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

-- 
David Stanaway