{"id":114,"date":"2009-03-20T18:18:24","date_gmt":"2009-03-20T22:18:24","guid":{"rendered":"http:\/\/www.dev-notes.com\/blog\/2009\/03\/20\/adding-or-subtracting-months-or-years-for-oracle-dates\/"},"modified":"2009-03-20T18:18:24","modified_gmt":"2009-03-20T22:18:24","slug":"adding-or-subtracting-months-or-years-for-oracle-dates","status":"publish","type":"post","link":"https:\/\/www.dev-notes.com\/blog\/2009\/03\/20\/adding-or-subtracting-months-or-years-for-oracle-dates\/","title":{"rendered":"Adding or subtracting months or years for Oracle dates"},"content":{"rendered":"<p>I ran into the need to do this because one of my users performed a big data import, and it was not until he finished that he realized somewhere along the way when he was preparing the data, instead of &#8220;2009&#8221;, some of the years came out to be &#8220;1909&#8221;.  To fix this in the database, I made use of Oracle&#8217;s built-in numtoyminterval() function, which stands for &#8220;Number to Year\/Month Interval&#8221;.  The syntax is as follows:<\/p>\n<pre class=\"code\">\nnumtoyminterval(n, interval_name)\n<\/pre>\n<p>&#8220;n&#8221; is the quantity, and &#8220;interval_name&#8221; is either &#8220;year&#8221; or &#8220;month&#8221;.  The following example illustrates its basic usage.<\/p>\n<pre class=\"code\">\nselect sysdate as now,\nsysdate + numtoyminterval(1,'month') as plus_1_month,\nsysdate + numtoyminterval(3,'month') as plus_3_months,\nsysdate + numtoyminterval(12,'month') as plus_12_months,\nsysdate + numtoyminterval(1,'year') as plus_1_year\nfrom dual;\n\nNOW       PLUS_1_MO PLUS_3_MO PLUS_12_M PLUS_1_YE\n--------- --------- --------- --------- ---------\n20-MAR-09 20-APR-09 20-JUN-09 20-MAR-10 20-MAR-10\n<\/pre>\n<p>Armed with this Oracle built-in function, I simply ran the following update statement to correct the bad data that my user had imported today.<\/p>\n<pre class=\"code\">\nupdate example_table\nset date_goes_here = date_goes_here + numtoyminterval(1,'year')\nwhere date_goes_here between '1-jan-1909' and '31-dec-1909'\nand trunc(entry_date,'ddd') = trunc(sysdate,'ddd')\nand entry_by = 'careless_user';\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>This note illustrate how we can add or subtract entire months or years from a given date without having to calculate the number of days to add or subtract, which may be complicated due to leap years, various months having different number of days, etc.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-114","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/114","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=114"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/114\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=114"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=114"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=114"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}