find last day of month

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

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