{"id":543,"date":"2014-03-03T17:38:43","date_gmt":"2014-03-03T16:38:43","guid":{"rendered":"http:\/\/www.epischel.de\/wordpress\/?p=543"},"modified":"2014-03-31T23:05:52","modified_gmt":"2014-03-31T21:05:52","slug":"the-oracle-add_months-pitfall","status":"publish","type":"post","link":"https:\/\/www.epischel.de\/wordpress\/2014\/03\/the-oracle-add_months-pitfall\/","title":{"rendered":"The Oracle &#8222;ADD_MONTHS&#8220; pitfall"},"content":{"rendered":"<p>The Oracle DB function &#8222;add_months&#8220; adds a given number of months to a given date:<\/p>\n<pre>SQL&gt; select add_months(to_date('<span style=\"color: #ff0000;\">04.03.14<\/span>','dd.mm.yy'),1) as result from dual;\r\n\r\nRESULT\r\n--------\r\n<span style=\"color: #ff0000;\">04.04.14<\/span><\/pre>\n<p>Nice. But what about 2014-02-28 plus 1 month?<\/p>\n<pre>SQL&gt; select add_months(to_date('<span style=\"color: #ff0000;\">28.02.14<\/span>','dd.mm.yy'),1) as result from dual;\r\n\r\nRESULT\r\n--------\r\n<span style=\"color: #ff0000;\">31.03.14<\/span><\/pre>\n<p>Ups. It turns out that (<a href=\"http:\/\/docs.oracle.com\/cd\/B19306_01\/server.102\/b14200\/functions004.htm\">quote from documentation<\/a>)<\/p>\n<blockquote><p>If <em>date<\/em> is <strong>the last day of the month<\/strong> or if the resulting month has fewer days than the day component of <em>date<\/em>, then the result is the <strong>last day of the resulting month<\/strong>.<\/p><\/blockquote>\n<p>The day component of the result can even be greater than the day component of the argument date:<\/p>\n<pre>SQL&gt; select add_months(to_date('<span style=\"color: #ff0000;\">28.02.14<\/span>','dd.mm.yy'),120) as result from dual;\r\n\r\nRESULT\r\n--------\r\n<span style=\"color: #ff0000;\">29.02.24<\/span><\/pre>\n<p>This was the root cause of defect in a partner system. Of course, there is already <a href=\"http:\/\/stackoverflow.com\/questions\/5349201\/add-months-function-does-not-return-the-correct-date-in-oracle\">a discussion on Stackoverflow<\/a> over this issue.<\/p>\n<p>As an alternative, you can use interval arithmetic:<\/p>\n<pre>SQL&gt; select to_date('<span style=\"color: #ff0000;\">28.02.14<\/span>','dd.mm.yy') + INTERVAL '10' YEAR as result from dual;\r\n\r\nRESULT\r\n--------\r\n<span style=\"color: #ff0000;\">28.02.24<\/span><\/pre>\n<p>Nice. But interval arithmetic is not of much help because it does not work with days that does not exist in every month.<\/p>\n<pre>SQL&gt; select to_date('<span style=\"color: #ff0000;\">28.02.14<\/span>','dd.mm.yy') + INTERVAL '10' YEAR as result from dual;\r\nselect to_date('28.02.14','dd.mm.yy') + INTERVAL '10' YEAR as result from dual\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\r\nERROR in Zeile 1:\r\nORA-01839: date not valid for month specified\r\n<\/pre>\n<p>Date arithmetic is not trivial, I guess.<\/p>\n<div class=\"syndication-links\"><\/div>","protected":false},"excerpt":{"rendered":"<p>The Oracle DB function &#8222;add_months&#8220; adds a given number of months to a given date: SQL&gt; select add_months(to_date(&#8218;04.03.14&#8242;,&#8217;dd.mm.yy&#8216;),1) as result from dual; RESULT &#8212;&#8212;&#8211; 04.04.14 Nice. But what about 2014-02-28 plus 1 month? SQL&gt; select add_months(to_date(&#8218;28.02.14&#8242;,&#8217;dd.mm.yy&#8216;),1) as result from dual; RESULT &#8212;&#8212;&#8211; 31.03.14 Ups. It turns out that (quote from documentation) If date is the&hellip;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"mf2_syndication":[],"webmentions_disabled_pings":false,"webmentions_disabled":false,"footnotes":""},"categories":[2],"tags":[67,74],"series":[],"class_list":["post-543","post","type-post","status-publish","format-standard","hentry","category-entwicklung","tag-oracle","tag-sql","kind-"],"kind":false,"_links":{"self":[{"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/posts\/543","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/comments?post=543"}],"version-history":[{"count":2,"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/posts\/543\/revisions"}],"predecessor-version":[{"id":545,"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/posts\/543\/revisions\/545"}],"wp:attachment":[{"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/media?parent=543"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/categories?post=543"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/tags?post=543"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/www.epischel.de\/wordpress\/wp-json\/wp\/v2\/series?post=543"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}