From: | Chris Browne <cbbrowne(at)acm(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: find last day of month |
Date: | 2005-12-09 20:27:15 |
Message-ID: | 607jaehv8c.fsf@dba2.int.libertyrms.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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.
--
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:
From | Date | Subject | |
---|---|---|---|
Next Message | Sven Willenberger | 2005-12-09 21:27:42 | Re: find last day of month |
Previous Message | David Rio Deiros | 2005-12-09 20:09:17 | Re: swap relations to be able to execute a left join |