Re: getting last day of month

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