Re: getting last day of month

Sergey,

Try this one:

CREATE OR REPLACE FUNCTION public.lastdayofmonth(date)
RETURNS date AS
'

select ((date_trunc(\'month\', $1) + interval \'1 month\') - interval
\'1 day\')::date;

'
LANGUAGE 'sql' VOLATILE;

Sergey Pariev wrote:

Hi all.
I need to find out the last day of current month. Currently I do the
trick with code below, but that's rather ugly way to do it IMHO. Could
anybody suggest me a better way ?

The following is my testing procedure :

CREATE or REPLACE FUNCTION test_findout_dates()
RETURNS integer AS $$
DECLARE
begin_date date;
end_date date;
current_month int;
current_year int;
last_day int;
BEGIN
current_month := extract ( month from now() ) ;
current_year := extract ( year from now() ) ;

begin_date := current_year || '-' || current_month || '-01' ;

last_day := 31;
begin
end_date := (current_year || '-' || current_month || '-'||
last_day) :: date;
last_day := 0 ;
exception
when others then
raise notice '31 doesnt cut for month %',current_month ;
end;

if last_day > 0 then
begin
last_day := 30;
end_date := (current_year || '-' || current_month || '-'||
last_day) :: date;
last_day := 0 ;
exception
when others then
raise notice '30 doesnt cut for month %',current_month ;
end;
end if;

if last_day > 0 then
begin
last_day := 29;
end_date := (current_year || '-' || current_month || '-'||
last_day) :: date;
last_day := 0 ;
exception
when others then
raise notice '29 doesnt cut for month %',current_month ;
end;
end if;

if last_day > 0 then
begin
last_day := 28;
end_date := (current_year || '-' || current_month || '-'||
last_day ) :: date;
last_day := 0 ;
exception
when others then
raise notice '28 doesnt cut for month %',current_month ;

end;
end if;

raise notice 'begin date is % ',begin_date;
raise notice 'end date is % ',end_date;

return 1;
END;
$$ LANGUAGE plpgsql ;

Thans in Advance, Sergey.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
Sinceramente,
Josué Maldonado.

... "Monogamia: ilusión falaz de establecer relaciones con una pareja a
la vez."

Sergey Pariev schrieb:

Hi all.
I need to find out the last day of current month. Currently I do the
trick with code below, but that's rather ugly way to do it IMHO. Could
anybody suggest me a better way ?

The following is my testing procedure :

CREATE or REPLACE FUNCTION test_findout_dates()
RETURNS integer AS $$
DECLARE
begin_date date;
end_date date;
current_month int;
current_year int;
last_day int;
BEGIN
current_month := extract ( month from now() ) ;
current_year := extract ( year from now() ) ;

begin_date := current_year || '-' || current_month || '-01' ;

last_day := 31;
begin
end_date := (current_year || '-' || current_month || '-'||
last_day) :: date;
last_day := 0 ;
exception
when others then
raise notice '31 doesnt cut for month %',current_month ;
end;

if last_day > 0 then
begin
last_day := 30;
end_date := (current_year || '-' || current_month || '-'||
last_day) :: date;
last_day := 0 ;
exception
when others then
raise notice '30 doesnt cut for month %',current_month ;
end;
end if;

if last_day > 0 then
begin
last_day := 29;
end_date := (current_year || '-' || current_month || '-'||
last_day) :: date;
last_day := 0 ;
exception
when others then
raise notice '29 doesnt cut for month %',current_month ;
end;
end if;

if last_day > 0 then
begin
last_day := 28;
end_date := (current_year || '-' || current_month || '-'||
last_day ) :: date;
last_day := 0 ;
exception
when others then
raise notice '28 doesnt cut for month %',current_month ;

end;
end if;

raise notice 'begin date is % ',begin_date;
raise notice 'end date is % ',end_date;

return 1;
END;
$$ LANGUAGE plpgsql ;

Thans in Advance, Sergey.

SELECT date_trunc('month',CURRENT_DATE) + interval '1 month' - interval
'1 day';

HTH
Tino Wildenhain

You could try :

SELECT int4(EXTRACT( DAYS FROM CURRENT_DATE + '1 month'::interval -
CURRENT_DATE ));

----------------------------------------------------------------------------
---------------
Patrick Fiche
email : patrick.fiche@aqsacom.com
tel : 01 69 29 36 18
----------------------------------------------------------------------------
---------------

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Sergey Pariev
Sent: jeudi 25 aout 2005 18:44
To: pgsql-general postgresql.org
Subject: [GENERAL] getting last day of month

Hi all.
I need to find out the last day of current month. Currently I do the
trick with code below, but that's rather ugly way to do it IMHO. Could
anybody suggest me a better way ?

The following is my testing procedure :

CREATE or REPLACE FUNCTION test_findout_dates()
RETURNS integer AS $$
DECLARE
begin_date date;
end_date date;
current_month int;
current_year int;
last_day int;
BEGIN
current_month := extract ( month from now() ) ;
current_year := extract ( year from now() ) ;

begin_date := current_year || '-' || current_month || '-01' ;

last_day := 31;
begin
end_date := (current_year || '-' || current_month || '-'||
last_day) :: date;
last_day := 0 ;
exception
when others then
raise notice '31 doesnt cut for month %',current_month ;
end;

if last_day > 0 then
begin
last_day := 30;
end_date := (current_year || '-' || current_month || '-'||
last_day) :: date;
last_day := 0 ;
exception
when others then
raise notice '30 doesnt cut for month %',current_month ;
end;
end if;

if last_day > 0 then
begin
last_day := 29;
end_date := (current_year || '-' || current_month || '-'||
last_day) :: date;
last_day := 0 ;
exception
when others then
raise notice '29 doesnt cut for month %',current_month ;
end;
end if;

if last_day > 0 then
begin
last_day := 28;
end_date := (current_year || '-' || current_month || '-'||
last_day ) :: date;
last_day := 0 ;
exception
when others then
raise notice '28 doesnt cut for month %',current_month ;

end;
end if;

raise notice 'begin date is % ',begin_date;
raise notice 'end date is % ',end_date;

return 1;
END;
$$ LANGUAGE plpgsql ;

Thans in Advance, Sergey.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend