Re: find last day of month

On Fri, 2005-12-09 at 15:27 -0500, Chris Browne wrote:
> "Andrus Moor" <eetasoft(at)online(dot)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