From: | Sven Willenberger <sven(at)dmv(dot)com> |
---|---|
To: | Chris Browne <cbbrowne(at)acm(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: find last day of month |
Date: | 2005-12-09 21:27:42 |
Message-ID: | 1134163662.7485.24.camel@lanshark.dmv.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Luciano Bastianello | 2005-12-09 22:13:05 | R: find last day of month |
Previous Message | Chris Browne | 2005-12-09 20:27:15 | Re: find last day of month |