Thread: getting last day of month

getting last day of month

From

Sergey Pariev

Date:

25 August 2005, 13:42:50

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.

Re: getting last day of month

From

Sergey Moiseev

Date:

25 August 2005, 13:59:04

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 ?
select '2005-09-01'::date-'1 day'::interval does the trick :)
--
Wbr, Sergey Moiseev

Re: getting last day of month

From

Tino Wildenhain

Date:

25 August 2005, 14:06:31

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

Re: getting last day of month

From

josue

Date:

25 August 2005, 14:06:52

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

Re: getting last day of month

From

Patrick.FICHE@AQSACOM.COM

Date:

25 August 2005, 14:12:30

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

Re: getting last day of month

From

Chris Browne

Date:

25 August 2005, 21:41:25

egy@tnet.dp.ua (Sergey Pariev) writes:
> 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 ?


log_analysis=# select date_trunc('months', (date_trunc('months', now()) + '45 days'::interval)) - '1 day'::interval;
        ?column?
------------------------
 2005-08-31 00:00:00+00
(1 row)

So...

log_analysis=# create or replace function eom (timestamptz) returns timestamptz as '
log_analysis'# select date_trunc(''months'', (date_trunc(''months'', $1) + ''45 days''::interval)) - ''1
day''::interval;'language sql; 
CREATE FUNCTION
log_analysis=# select eom(now());
          eom
------------------------
 2005-08-31 00:00:00+00
(1 row)
log_analysis=# select eom('2004-07-02');
          eom
------------------------
 2004-07-31 00:00:00+00
(1 row)

log_analysis=# select eom('2004-02-29');
          eom
------------------------
 2004-02-29 00:00:00+00
(1 row)
log_analysis=# select eom('2004-02-29'), eom('2005-02-28'), eom('2005-03-01');
          eom           |          eom           |          eom
------------------------+------------------------+------------------------
 2004-02-29 00:00:00+00 | 2005-02-28 00:00:00+00 | 2005-03-31 00:00:00+00
(1 row)
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/emacs.html
Q: How many Newtons does it take to change a light bulb?
A: Faux!  There to eat lemons, axe gravy soup!