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."