Re: Is there a better way than this to get the start and end of a month?

From: Dmitry Tkach <dmitry(at)openratings(dot)com>
To: David Stanaway <david(at)stanaway(dot)net>
Subject: Re: Is there a better way than this to get the start and end of a month?
Date: 2002-09-25 14:39:44
Message-ID: 3D91CAB0.7080003@openratings.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2002-09-25 16:13:21 Re: Timestamp Error - 7.2
Previous Message Dmitry Tkach 2002-09-25 14:28:08 Re: Is there a better way than this to get the start and end of a month?