Re: SELECT ( TO_DATE( '1995-04-30 00:00:00') + INTERVAL(-2) MONTH

If you could do the corre3ct procedure you can invoke it in a select with no problem as long as it return just one value. Chucho! Irfan Bondre wrote: > "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/ > > > > sending to informix-list