RE: Adding a month
Perhaps a better way would be the following
Select MDY(month(datum)+1,day(datum), year(datum))
Where month(datum) != 12
Union
Select MDY(1,day(datum),year(datum)+1)
Where month(datum) = 12
(check for syntax errors)
This assumes the rule that the 17th of February is one month past the
17th of January.
-----Original Message-----
From: David Coburn [SMTP:dcoburn@worldvision.org]
Posted At: Tuesday, September 22, 1998 2:21 PM
Posted To: Informix
Conversation: Adding a month
Subject: Re: Adding a month
Won't work in all cases. Look at the following SQL:
create table t1
(
my_date date
);
insert into t1 values ('01/01/98');
insert into t1 values ('01/31/98');
insert into t1 values ('02/01/98');
insert into t1 values ('02/28/98');
select my_date, my_date +1 units month
from t1;
drop table t1;
The results of this are:
Table created.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
my_date (expression)
01/01/98 1998-02-01
1267: The result of a datetime computation is out of
range.
Error in line 12
Near character position 5
The problem is the old classic: what do you do with February (or
any other
month with fewer than 31 days)?
David
"David C. Harrison" <dch@dchsoftware.co.uk> on 09/22/98 06:46:19
AM
Please respond to "David C. Harrison" <dch@dchsoftware.co.uk>
To: informix-list@iiug.org
cc: (bcc: David Coburn/ISG/WVUS/WorldVision)
Subject: Re: Adding a month
Zeljan,
> I have a column datum (date type) in table. I would like to
find a date
> one month later. So I would like my result to be:
> datum datum + 1 month
> 1998-1-17 1998-2-17
>
> I have tried with extend, but no luck.
SELECT (datum +1 UNITS MONTH) FROM table_name;
Enjoy!
-- David
______________________________________
David C. Harrison
DCH Software Limited
E-Mail: dch@dchsoftware.co.uk
Web: http://www.dchsoftware.co.uk