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

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

Well, month_end could be more straightforward as something like

select month_start ($1) + interval '1 day' - interval '1 month';

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

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