DATE and TIMESTAMP and how to deal with these was already the topic of some blog postings. But today I'm talking again about this - and I have a particular issue: Adding or substracting months from a given date. The most elegant syntax seems to be the INTERVAL function ...

What is that? Now, Oracle substracts a month from "October" and keeps the day (31st). The result is 31st of September - which does not exist, of course. This behaviour is odd - but it is as it is. So we have to figure how to deal with it. And there are two options: The first one would be to substract the month from the beginning of the next month. So we first "navigate" to November 1st, substract a month and a day. This is a bit of playing with ROUND and TRUNC functions for dates. But it is a quite cumbersome approach.

So when it is about adding or substracting months from a given date, the ADD_MONTHS function is the better approach since it also handles the described issue. And this applies particluarly when the given date is the end of a month, say: all days starting with the 28th. Yes, also the 29th and the 30th might be a problem, when your "target month" after adding or substracting is February.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.