AddMonths built-in function in an Oracle simple query condition

AddMonths built-in function in an Oracle simple query condition

Hi, All.

I tried an "AddMonths" function in a simple query condition and it resulted in an Oracle error (ORA-01839: "date not valid for month specified").

As far as I could see, this is due to the use of Oracle NUMTOYMINTERVAL function, to add months to the desired date; but, in these particular circumstances, it doesn't seem to work as well as the Oracle ADD_MONTHS  function does, i.e., when I add one month to #2011-01-31#, the latter returns  #2011-02-28#, whereas NUMTOYMINTERVAL returns the error.

Apparently, ADD_MONTHS treats the end of the month in a particular fashion, and  NUMTOYMINTERVAL doesn't; the result is an output that doesn't exist (#2011-02-31#), hence, the error.
Any comments (besides "stop using AddMonths in Oracle simple query conditions")

Hi Henrique,

I think you'd need to sort out first how you would like a AddMonth to behave.

- Would you expect 28 days to be added? (Actually adding up four weeks)
- Would you expect the function to jump to the 28th of the next month? (what if you're on October 31st; would you expect a 1 December to be returned?)
- Would you expect the function to jump to the last day of the next month?

As soon as you know what you need you can see whether there are functions available that can assist you.
You might want to use an itnernal Oracle function in case this is something that needs to be done e.g. on an Entire table.

I believe there also is an Extension that might help you; can't find it at the moment though.
(other than this one:
Hi, Eric.

First of all, thanks for the suggestion.

That being said, I should add that my post is not about what I like as much as what I don't want: i.e., if I have a problem with a date calculation, there's a number of workarounds, and you explain that very well; but, even with a solution to that problem, I don't think the platform should "blow" when you use an apparently harmless built-in.

I just think that, whatever Outsystems AddMonths() implements (28 days, last day of month, etc.), it shouldn't return an error when properly used.

If you think it's a bug, you should report it as such imho.

Hi Henrique,

I actually don't know about the internals of the AddMonth() Outsystems function but as I conclude from your story it seems to be using Oracle to do this.
This on it's own sounds kind of strange to me since this implicates that depending of the database the evaluation of the function can differ that is if MS and Oracle decide to handle the datetime in a different manner.

As per default if a datetime 'timestamp' is given back by Oracle this should be a default Unix timestamp and should also be translated in the correct manner.
This doesn't seem to be the case for which I agree with both you and Joost that this should be reported as a bug.
Also this might be a function that Outsystems want to cope with in it's own manner (not related to both the .NET or Java stack and not related to the Database chosen)

You could ofcourse write your own Outsystems function to handle this correctly although it think this is something you can expect to be provided.
You can use the MOD function to decide whether the month would be even or odd and for February you can cope with the leap year if the year can be divided by for (also using a MOD math function)

Two small hints
- Mind you that adding say 3 months to 31/12/2003 results in an extra day for the 2004 leap year so first calculate
whether you're going to be after february 28th of a leap year and then just add days based on the default number of days.
- You can also jump over multiple leap years so don't fix that in a boolean

Since you are not specifying time we leave out the leap second in this equation. :)
Should you be eager to create a exact function:



Thanks for the input and sorry for all the confusion.

Actually, I should have said, for starters, that there's what I consider my conceptual error: that AddMonths() built-in shouldn't be used in a query condition; but it can be used, it's allowed and so, I tried it.

That being said, I think - but am not sure - it is a bug, since there's a difference between versions (just checked): using, Outsystems AddMonths() was converted to Oracle "ADD_MONTHS" and it worked fine; with the new it's converted to "NUMTOYMINTERVAL" and that means an ORA-01839: "date not valid for month specified" error.

Probably better if reported to support, like Joost suggested.

Clear and agree. :)