Re: find last day of month - Mailing list pgsql-general

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