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

Irfan Bondre wrote: > "Jonathan Leffler" <jleffler@earthlink.net> 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. [...] > > The correct answer for all those is.. > 28th Feb in case of NON-Leap Year and 29th Feb in case of Leap Year. I suspect that you overlooked 31st March + 1 month in your analysis; I defy you to come up with any date in February in any year for that calculation. :-) You probably meant 30th April for that example. > I need to accomplish all that in a single SELECT Query.... For those in the audience who are completely hamstrung by the DBA for the systems they use, the CASE statement does actually permit it to be done inline. It isn't pretty, and it would be stupid to write the code out more than once - functions were invented to prevent you writing out complex logic more than once! Assumptions: datecol is the DATE value you start with, and nmonths is the INTEGER number of months to add or subtract. CASE WHEN MONTH(DATE(EXTEND(MDY(MONTH(datecol), 1, YEAR(datecol)), YEAR TO DAY) + nmonths UNITS MONTH)) = MONTH(DATE(EXTEND(MDY(MONTH(datecol), 1, YEAR(datecol)), YEAR TO DAY) + nmonths UNITS MONTH) + DAY(datecol) - 1) THEN DATE(EXTEND(MDY(MONTH(datecol), 1, YEAR(datecol)), YEAR TO DAY) + nmonths UNITS MONTH) + DAY(datecol) - 1 ELSE DATE(EXTEND(MDY(MONTH(datecol), 1, YEAR(datecol)), YEAR TO DAY) + nmonths UNITS MONTH) + DAY(datecol) - 1 - DAY(DATE(EXTEND(MDY(MONTH(datecol), 1, YEAR(datecol)), YEAR TO DAY) + nmonths UNITS MONTH) + DAY(datecol) - 1) END It looks like LISP - Lots of Irritating Silly Parentheses - and illustrates why pure functional programming is a pain (so much so that modern variants of LISP have variables too - not to mention that you'd define yourself a function anyway). In fact, that expression is so ghastly that it is pretty damn silly to write it out even once! Personally, I'd be extremely upset with anyone who presented me with that code to review -- use a stored procedure! I'd be looking for them to repent immediately, or for them to resign and seek employment somewhere else. The statement is unmaintainable. I'd hate to guess what the optimizer does with it -- would it factor out all the common sub-expressions? http://www.catb.org/~esr/faqs/smart-questions.html -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/