Thread: find last day of month

I have a table containing month column in format mm.yyyy

create table months ( tmkuu c(7));
insert into months values ('01.2005');
insert into months values ('02.2005');

How to create select statement which converts this column to date type
containing last day of month like

'2005-01-31'
'2005-02-28'

Andrus.

Andrus Moor wrote:

>I have a table containing month column in format mm.yyyy
>
>create table months ( tmkuu c(7));
>insert into months values ('01.2005');
>insert into months values ('02.2005');
>
>How to create select statement which converts this column to date type
>containing last day of month like
>
>'2005-01-31'
>'2005-02-28'
>
>Andrus.
>
>
select to_date(tmkuu, 'mm.yyyy') + '1 month'::interval - '1
day'::interval from months; will convert it to a timestamp.  You can
further downcast to date if you need to.

Jeff

"Andrus Moor" <eetasoft@online.ee> writes:

> I have a table containing month column in format mm.yyyy
>
> create table months ( tmkuu c(7));
> insert into months values ('01.2005');
> insert into months values ('02.2005');
>
> How to create select statement which converts this column to date type
> containing last day of month like
>
> '2005-01-31'
> '2005-02-28'
>

As long as you are using DateSytle = DMY the following should work:


ebacon=# select * from months;
  tmkuu
---------
 01.2005
 02.2005
 12.2004
(3 rows)

ebacon=# select (('01.' || tmkuu)::date + interval '1 month' - interval '1 day')::date from months;
    date
------------
 2005-01-31
 2005-02-28
 2004-12-31
(3 rows)

"Andrus Moor" <eetasoft@online.ee> writes:

> I have a table containing month column in format mm.yyyy
>
> create table months ( tmkuu c(7));
> insert into months values ('01.2005');
> insert into months values ('02.2005');
>
> How to create select statement which converts this column to date type
> containing last day of month like
>
> '2005-01-31'
> '2005-02-28'

The usual trick is to split it into year and month, add 1 to the
month, if that's > 12, jump to 1, and add a year.

Based on those...  Construct the first day of the NEXT month.

Thus...  01.2005
 --> month = 2
 --> year = 2005

Construct first day of the next month:
   2005-02-01

Now, subtract a day from that, and you'll get the final day of the
present month.

That approach will nicely cope with leap years and such.
--
let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/lisp.html
"When the grammar checker identifies an error, it suggests a
correction and can even makes some changes for you."
-- Microsoft Word for Windows 2.0 User's Guide, p.35:

On Fri, 2005-12-09 at 15:27 -0500, Chris Browne wrote:
> "Andrus Moor" <eetasoft@online.ee> writes:
>
> > I have a table containing month column in format mm.yyyy
> >
> > create table months ( tmkuu c(7));
> > insert into months values ('01.2005');
> > insert into months values ('02.2005');
> >
> > How to create select statement which converts this column to date type
> > containing last day of month like
> >
> > '2005-01-31'
> > '2005-02-28'
>
> The usual trick is to split it into year and month, add 1 to the
> month, if that's > 12, jump to 1, and add a year.
>
> Based on those...  Construct the first day of the NEXT month.
>
> Thus...  01.2005
>  --> month = 2
>  --> year = 2005
>
> Construct first day of the next month:
>    2005-02-01
>
> Now, subtract a day from that, and you'll get the final day of the
> present month.
>
> That approach will nicely cope with leap years and such.

or simply:

test=>select ((split_part('12.2005','.',2) || '-' ||
split_part('12.2005','.',1) || '-01')::date + interval '1 mon' -
interval '1 day')::date;
    date
------------
 2005-12-31

test=>select ((split_part('02.2008','.',2) || '-' ||
split_part('02.2008','.',1) || '-01')::date + interval '1 mon' -
interval '1 day')::date;
    date
------------
 2008-02-29

Sven