12. Oktober 2010

Monate zu einem Datum hinzufügen. Da gibt's doch nix zu sagen, oder doch?

When it is about adding months ...
Das Thema DATE und TIMESTAMP hatte ich zwar schon, aber aus gegebenem Anlaß möchte ich heute doch noch ein kurzes Posting nachschieben. Es geht darum, wie man am effizientesten Monate zu einem Datum hinzufügt oder von diesem abzieht. Die eleganteste Variante scheint die INTERVAL-Syntax zu sein ...
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 ...
SQL> select to_date('2010-10-12', 'YYYY-MM-DD') - interval '1' month datum 
  2  from dual;

DATUM
----------
12.09.2010

1 Zeile wurde ausgewählt.
Sieht erstmal gut aus ... aber wenn man das mal mit dem 31.10. probiert, erlebt man eine Überraschung ...
This looks good at the first glance. But when you try this with the 31st of a month (say: October) you encounter an error ...
SQL> select to_date('2010-10-31', 'YYYY-MM-DD') - interval '1' month datum 
  2  from dual;

FEHLER in Zeile 1:
ORA-01839: Datum für angegebenen Monat nicht gültig
Was soll das? Naja, Oracle nimmt vom 31.10. einen Monat weg und kommt dann auf den 31.09. - den gibt es aber nicht, also wird ein Fehler ausgelöst. Das ist nicht so schön - aber nicht zu ändern. Es gibt zwei Möglichkeiten, mit dem Problem umzugehen. Zunächst könnte man mit dem "nächsten Ersten" arbeiten. Also nicht einen Monat vom 31.10. abziehen, sondern erstmal zum nächsten Ersten gehen (1.11.), dann den Monat abziehen und wieder zurückgehen. Das ist dann ein wenig Spielen mit TRUNC und ROUND. Alles in allem eher aufwändig.
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.
Oder man verwendet die "alte" Funktion ADD_MONTHS. Anders als der Name vermuten lässt, kann man damit auch Monate abziehen.
The old fashioned function ADD_MONTHS is much easier: It can not only add but also substract months.
SQL> select add_months(to_date('2010-10-31', 'YYYY-MM-DD'), -1) from dual;

ADD_MONTHS
----------
30.09.2010
Also: wenn es darum geht, "Monate" von einem Datum abzuziehen oder dazuzurechnen, ist ADD_MONTHS die einfachere Variante - sie erfordert keine Sonderbehandlung für die Tage ab dem 28. eines Monats. Sogar der 29. oder der 30. können zum beschriebenen Fehler führen - wenn man nach dem Abziehen oder Hinzufügen der Monate im Februar landen würde.
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.

Kommentare:

Volker hat gesagt…

bei add_months ist noch zubeachten, dass man bei Daten am Monatsende wieder am Monatsende landet: add_months('31.3.2010',-1) liefert so den 28.2.2010.

Frank hat gesagt…

... und auch die Nebeneffekte:

ADD_MONTHS('29.03.2010',-1) liefert den 28.02.2010, wendet man auf dieses Ergebnis dann nochmals ADD_MONTHS('28.02.2010',-1) an, so erhält man 31.01.2010.

Also aufgepasst, da ADD_MONTHS('29.03.2010',-2) selbstverständlich 29.01.2010 liefert.

Carsten Czarski hat gesagt…

Hallo,

ADD_MONTHS ist eben so definiert, dass er grundsätzlich mal einen Monat hinzufügt oder abzieht. Ist das Ausgangsdatum aber der letzte Tag des Monats, so "navigiert" er zum letzten Tag des Folge- bzw. vorhergehenden Monats. Daher der Interessante Effekt, dass ein ADD_MONTHS('27.02.2010',1) auf den 27.3. geht, ein ADD_MONTHS('28.02.2010') jedoch direkt auf den 31.03. Der Monatsultimo wird hier gesondert behandelt.

Grüße

-Carsten

Anonym hat gesagt…

Was ist dann der Nutzen der "interval"-Funktion (Datumsbereich, nicht für Minuten oder ähnliches), wenn die nicht mal so etwas "triviales" wie korrekte Monatsberechnungen macht? *Natürlich* gibt es keinen 31.09., aber eben deshalb verwendet man doch Datumsfunktionen, oder?

Bin ich froh, dass ich bisher immer noch mit add_months gearbeitet habe, damit ist mir eine böse Stolperfalle entgangen.

Carsten Czarski hat gesagt…

Hallo "Anonym",

ich sehe das absolut genauso ... habe aber heute nochmal nachgesehen, ob das wirklich ein Oracle-Problem ist oder ob es gar tiefere Gründe dafür gibt. Interessierte können ja mal in die Metalink-Note 456704.1 schauen.
Es läuft darauf hinaus, dass dieses Verhalten sich aus dem SQL92-Standard ergibt - und es genau so sein soll ... und ADD_MONTHS ist die Lösung für das Problem ...

Beste Grüße

-Carsten

Beliebte Postings