Search code examples
oracledate-arithmetic

How to add a day with a specific date using add_months function

I am trying to add a day with a specific date using add_months in oracle database. I wrote this line:

SELECT ADD_MONTHS('01-JAN-2018', MONTHS_BETWEEN('02-JAN-2018', '01-JAN-2018')) FROM DUAL;

this returns:

01-JAN-18

Why doesn't it return 02-JAN-18?? Can I add one day to the date using this function?

Solution

  • Why doesn't it return 02-JAN-18??

    According to MONTHS_BETWEEN documentation,

    The MONTHS_BETWEEN function calculates the number of months between two dates. When the two dates have the same day component or are both the last day of the month, then the return value is a whole number. Otherwise, the return value includes a fraction that considers the difference in the days based on a 31-day month

    So, 

    select MONTHS_BETWEEN('02-JAN-2018', '01-JAN-2018') FROM DUAL ;

    yields

    .0322580645161290322580645161290322580645

    ADD_MONTHS returns the date date plus integer months. So, .0322.. is considered as integer 0 and your query is equivalent to

    SELECT ADD_MONTHS('01-JAN-2018', 0) FROM DUAL;

    In order to add 1 months, simply take the difference of two dates.

    SELECT ADD_MONTHS(DATE '2018-01-01', DATE '2018-01-02' - DATE '2018-01-01') FROM DUAL;

    Or better, add an INTERVAL of 1 month

    SELECT DATE '2018-01-01' + INTERVAL '1' MONTH FROM DUAL;

    To answer your question, add 1 day, simply use

    SELECT DATE '2018-01-01' + 1 FROM DUAL;