SELECT ( TO_DATE( '1995-04-30 00:00:00') + INTERVAL(-2) MONTH TO MONTH) from table ( set{1} )

SELECT ( TO_DATE( '1995-04-30 00:00:00') + INTERVAL(-2) MONTH TO MONTH) from table ( set {1} ) Returns the following error SQLSTATE = S1000 NATIVE ERROR = -1267 MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]-1267 This is because substracting 2 Months from '1995-04-30 00:00:00' results in '1995-02-30 00:00:00' i.e february 30, which doesn't eixt. Hence the error. Any way around. Irfan.

Irfan Bondre wrote: > SELECT ( TO_DATE( '1995-04-30 00:00:00') + INTERVAL(-2) MONTH TO MONTH) > from table ( set {1} ) > Returns the following error > SQLSTATE = S1000 > NATIVE ERROR = -1267 > MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]-1267 > > > This is because substracting 2 Months from '1995-04-30 00:00:00' results in > '1995-02-30 00:00:00' i.e february 30, which doesn't eixt. Hence the error. > > Any way around. One common suggestion is to work in days, not months. There is, of course, plenty to worry about with that solution. Do a Google search within c.d.i. - there is plenty of discussion about this particular issue. -- June Hunt

Its difficult working with days for the above query. I would need to find the number of days in the intermediate months, figure out if its a leap year... Does informix provide any SQL functions to deal with those? Thanks Irfan "June C. Hunt" <june_c_hunt@hotmail.com> wrote in message news:415B4DA0.50506@hotmail.com... > Irfan Bondre wrote: > > SELECT ( TO_DATE( '1995-04-30 00:00:00') + INTERVAL(-2) MONTH TO MONTH) > > from table ( set {1} ) > > Returns the following error > > SQLSTATE = S1000 > > NATIVE ERROR = -1267 > > MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]-1267 > > > > > > This is because substracting 2 Months from '1995-04-30 00:00:00' results in > > '1995-02-30 00:00:00' i.e february 30, which doesn't eixt. Hence the error. > > > > Any way around. > > One common suggestion is to work in days, not months. There is, of > course, plenty to worry about with that solution. Do a Google search > within c.d.i. - there is plenty of discussion about this particular issue. > > -- > June Hunt

Basically my task is to achieve the a MonthAdd Function Which takes a datetime as argument and number of months to add or subtract and returns a new date... Thanks Irfan. "Irfan Bondre" <ibondre@yahoo.com> wrote in message news:8da2c1b2.0409291328.4cb08e43@posting.google.com... > SELECT ( TO_DATE( '1995-04-30 00:00:00') + INTERVAL(-2) MONTH TO MONTH) > from table ( set {1} ) > Returns the following error > SQLSTATE = S1000 > NATIVE ERROR = -1267 > MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]-1267 > > > This is because substracting 2 Months from '1995-04-30 00:00:00' results in > '1995-02-30 00:00:00' i.e february 30, which doesn't eixt. Hence the error. > > Any way around. > > Irfan.

Irfan Bondre wrote: > Basically my task is to achieve the a MonthAdd Function Which takes > a datetime as argument and number of months to add or subtract and > returns a new date... Fine. Now define the correct answer for 30th April minus 2 months; 29th April minus 2 months in a leap year; 29th April minus 2 months in a non-leap year; 28th April minus two months; 31st March - 1 month; 31st March + 1 month. Assuming your answers are self-coherent, you can probably determine how to deal with it. Or you can ask for help. But...we can't help you unless you tell us the answers to all those questions - and I'm assuming that the answers to auxilliary questions such as 29th March - 1 month in a leap year are similar to the 29th April - 2 months in a leap year answer (that's the self-coherency criterion at work). Consider whether this works: CREATE PROCEDURE MonthAdd(d DATE, i INTEGER) RETURNING DATE AS retval; DEFINE d1 DATE; DEFINE rv DATE; LET d1 = MDY(MONTH(d), 1, YEAR(d)); -- First day of given month LET rv = EXTEND(d1, YEAR TO DAY) + i UNITS MONTH; -- Add i months LET rv = rv + (d - d1); -- Add the days back IF MONTH(rv) != MONTH(d) THEN -- If the month changed LET rv = rv - DAY(rv); -- Subtract the number of days -- to get last day of prior month END IF; RETURN rv; END PROCEDURE; The code has not been anywhere near a database server - syntax errors and worse are possible. It's just an idea; a couple of minutes thought while typing suggested the answer. Note that adding months to the first of a month always yields an answer and not an error (well, unless you go out of the range of valid dates). This code exploits that. > "Irfan Bondre" <ibondre@yahoo.com> wrote: > >>SELECT ( TO_DATE( '1995-04-30 00:00:00') + INTERVAL(-2) MONTH TO MONTH) >>from table ( set {1} ) >>Returns the following error >>SQLSTATE = S1000 >>NATIVE ERROR = -1267 >>MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]-1267 >> >> >> This is because substracting 2 Months from '1995-04-30 00:00:00' >> results in '1995-02-30 00:00:00' i.e february 30, which doesn't >> eixt. Hence the error. Any way around. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler wrote: > Irfan Bondre wrote: > >> Basically my task is to achieve the a MonthAdd Function Which takes >> a datetime as argument and number of months to add or subtract and >> returns a new date... > > Fine. Now define the correct answer for 30th April minus 2 months; 29th > April minus 2 months in a leap year; 29th April minus 2 months in a > non-leap year; 28th April minus two months; 31st March - 1 month; 31st > March + 1 month. Assuming your answers are self-coherent, you can > probably determine how to deal with it. Or you can ask for help. > > But...we can't help you unless you tell us the answers to all those > questions - and I'm assuming that the answers to auxilliary questions > such as 29th March - 1 month in a leap year are similar to the 29th > April - 2 months in a leap year answer (that's the self-coherency > criterion at work). > > Consider whether this works: > > CREATE PROCEDURE MonthAdd(d DATE, i INTEGER) RETURNING DATE AS retval;> > DEFINE d1 DATE; > DEFINE rv DATE; > > LET d1 = MDY(MONTH(d), 1, YEAR(d)); -- First day of given month > LET rv = EXTEND(d1, YEAR TO DAY) + i UNITS MONTH; -- Add i months > LET rv = rv + (d - d1); -- Add the days back > IF MONTH(rv) != MONTH(d) THEN -- If the month changed This condition is bogus - 100% bogus. The concept is approximately right; the implementation is all wrong. You probably need to preserve the value of rv before adding the days back, as well as the value with the days added back, and check that they refer to the same month. > LET rv = rv - DAY(rv); -- Subtract the number of days > -- to get last day of prior month You need to verify that this logic works correctly for negative increments. I think it is OK - you need to verify it. > END IF; > > RETURN rv; > > END PROCEDURE; > > The code has not been anywhere near a database server - syntax errors > and worse are possible. It's just an idea; a couple of minutes thought > while typing suggested the answer. Note that adding months to the first > of a month always yields an answer and not an error (well, unless you go > out of the range of valid dates). This code exploits that. > > >> "Irfan Bondre" <ibondre@yahoo.com> wrote: >> >>> SELECT ( TO_DATE( '1995-04-30 00:00:00') + INTERVAL(-2) MONTH TO MONTH) >>> from table ( set {1} ) >>> Returns the following error >>> SQLSTATE = S1000 >>> NATIVE ERROR = -1267 >>> MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]-1267 >>> >>> >>> This is because substracting 2 Months from '1995-04-30 00:00:00' >>> results in '1995-02-30 00:00:00' i.e february 30, which doesn't >>> eixt. Hence the error. Any way around. > > > > -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

"Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:415B99B3.4020507@earthlink.net... > Irfan Bondre wrote: > > > Basically my task is to achieve the a MonthAdd Function Which takes > > a datetime as argument and number of months to add or subtract and > > returns a new date... > > Fine. Now define the correct answer for 30th April minus 2 months; > 29th April minus 2 months in a leap year; 29th April minus 2 months in > a non-leap year; 28th April minus two months; 31st March - 1 month; > 31st March + 1 month. Assuming your answers are self-coherent, you > can probably determine how to deal with it. Or you can ask for help. > > But...we can't help you unless you tell us the answers to all those > questions - and I'm assuming that the answers to auxilliary questions > such as 29th March - 1 month in a leap year are similar to the 29th > April - 2 months in a leap year answer (that's the self-coherency > criterion at work). The correct answer for all those is.. 28th Feb in case of NON-Leap Year and 29th Feb in case of Leap Year. I can't create a procedure, I need to accomplish all that in a single SELECT Query.... Irfan. > > Consider whether this works: > > CREATE PROCEDURE MonthAdd(d DATE, i INTEGER) RETURNING DATE AS retval;> > DEFINE d1 DATE; > DEFINE rv DATE; > > LET d1 = MDY(MONTH(d), 1, YEAR(d)); -- First day of given month > LET rv = EXTEND(d1, YEAR TO DAY) + i UNITS MONTH; -- Add i months > LET rv = rv + (d - d1); -- Add the days back > IF MONTH(rv) != MONTH(d) THEN -- If the month changed > LET rv = rv - DAY(rv); -- Subtract the number of days > -- to get last day of prior month > END IF; > > RETURN rv; > > END PROCEDURE; > > The code has not been anywhere near a database server - syntax errors > and worse are possible. It's just an idea; a couple of minutes > thought while typing suggested the answer. Note that adding months to > the first of a month always yields an answer and not an error (well, > unless you go out of the range of valid dates). This code exploits that. > > > > "Irfan Bondre" <ibondre@yahoo.com> wrote: > > > >>SELECT ( TO_DATE( '1995-04-30 00:00:00') + INTERVAL(-2) MONTH TO MONTH) > >>from table ( set {1} ) > >>Returns the following error > >>SQLSTATE = S1000 > >>NATIVE ERROR = -1267 > >>MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]-1267 > >> > >> > >> This is because substracting 2 Months from '1995-04-30 00:00:00' > >> results in '1995-02-30 00:00:00' i.e february 30, which doesn't > >> eixt. Hence the error. Any way around. > > > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/