getting last day of month

Поиск
Список
Период
Сортировка
От Sergey Pariev
Тема getting last day of month
Дата
Msg-id 430DF53E.9010103@tnet.dp.ua
обсуждение исходный текст
Ответы Re: getting last day of month  (Sergey Moiseev <bopm@udaff.com>)
Re: getting last day of month  (Tino Wildenhain <tino@wildenhain.de>)
Re: getting last day of month  (josue <josue@lamundial.hn>)
Список pgsql-general
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.


В списке pgsql-general по дате отправления:

Предыдущее
От: Tino Wildenhain
Дата:
Сообщение: Re: Postgresql replication
Следующее
От: Sergey Moiseev
Дата:
Сообщение: Re: getting last day of month