Informix Date Arithmetic - Subtracting Months

[mz...@my-dejanews.com]

When I run the following query, I get an error on some records:

select begin_date,end_date
end_date - begin_date,
rptend - INTERVAL(6) MONTH TO MONTH
from table_a

ERROR: 1267: The result of a datetime computation is out of range.

I am trying to subtract 6 months from a date. It appears
that subtracting 6 months from a date like 8/31/1997 is causing
a problem because 02/31/1997 is not a valid date.

I was wondering if someone had a work-around for this.

Thanks,

Mike

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading

[mz...@my-dejanews.com]

[Andrew Mercer]

One of our programmers had a similar problem adding 2 months. I suggested he
add 60 days which was adequate for his requirements.

Hope this helps

mz...@my-dejanews.com wrote in message <6mrpnf$itd$1...@nnrp1.dejanews.com>...

[jpa...@epsilon.com]

Must be the day for it. I happen to have a routine to this properly as
well. I'll be sure to snag it for you.

cheers
j.

"Andrew Mercer" <ame...@wsl.com.au> on 06/24/98 08:20:28 PM

Please respond to "Andrew Mercer" <ame...@wsl.com.au>

To: inform...@iiug.org
cc: (bcc: Jack Parker/Boston50/Epsilon)
Subject: Re: Informix Date Arithmetic - Subtracting Months

[David Coburn]

As a rule of thumb, you can't do certain forms of date arithmetic if the
date values in question contain a month value. In other words, you could
subtract your interval(6) month to month from a datetime year to month, but
not year to day (which is essentially what the date values are). This
makes absolutely no sense if you are thinking about the first of a month,
i.e., 1-JUN less three months is 1-MAR. However, come up with a universal
rule for subtracting one month from 28-FEB. Will it be 28-JAN? How about
31-JAN? Don't even start discussing this, BTW; this gets argued
periodically in various formats and the answer is still the same: you
can't. In fact, people have gone to war for less.

There are various "fixes" to this out there. Check the archives and I'm
sure you'll find something.

HTH,

David

[mos...@wellsfargo.com]

Just an idea...

I had a 4GL application that had to run on the last day of every month, and
calculate back 6 months. Basically, this is how I found the "6 months ago"
date:

# You don't have to use "today" here; you could start with any date
LET date1 = mdy(month(today), 1, year(today))

# Go back one month less than your goal
LET date2 = date1 - 5 units month

# Going back one more day puts you at the last day of the month, 6 mos. ago
LET six_mo_date = date2 - 1 units day

Of course, you probably COULD put this all together into on big LET stmt.
Regardless, maybe this will give you some ideas...

HTH
================================
Paul A. Mosser, Open Systems DBA
Wells Fargo & Co.
Tempe, Arizona
mos...@wellsfargo.com
================================

> -----Original Message-----
> From: David Coburn [mailto:dco...@worldvision.org]
> Sent: Thursday, June 25, 1998 11:07 AM
> To: inform...@iiug.org
> Subject: Re: Informix Date Arithmetic - Subtracting Months
>
>