CODEWITHSUNDEEP
sqloracle-database
Nikki
Nikki

Reputation: 3

add a partial month to a date in oracle

I am trying to add a partial month to a date in oracle. i tried like this

SELECT months_between('1-DEC-2014',SYSDATE) FROM dual

i get -2.79041218637993

then i tried to add (subtract) this result to sysdate.

SELECT add_months(trunc(SYSDATE), -2.79041218637993) FROM dual

i thought i should have gotten '1-DEC-2014' but it only added the 2 full months and left the .79041218637993 out so it returned 12/25/2014.

any ideas how i can get it to add the partial month as well so that it will return 12/01/2014?

Upvotes: 0

Views: 617

Answers (2)

dimm
dimm

Reputation: 1798

Subtract 2 months and then trunc to month.

SELECT TRUNC(add_months(trunc(SYSDATE), -2),'MM') FROM dual

Upvotes: 0

Jean-François Savard
Jean-François Savard

Reputation: 21004

Use the add_months function:

select add_months(sysdate, 2) from dual;

Edit : To answer your comment, simply use

select sysdate + days_between('1-DEC-2014',SYSDATE) from dual;

Upvotes: 1

Related Questions