sql - Add 2 months to current timestamp

Question

How can I add months to a timestamp value in Oracle? In my query, it's getting converted to date value instead:

     SELECT add_months(current_timestamp,2) 
     FROM   dual;

The actual output is:

     ADD_MONTH
     11-MAR-13

The expected output is:

    2013-01-01 00:00:00.000000000+00:00

Answer 1

This will give you the date and the time as a TIMESTAMP data type:

select TO_TIMESTAMP(TO_CHAR(ADD_MONTHS(SYSDATE, 2), 'YYYYMMDD HH24:MI'), 
'YYYYMMDD HH24:MI') from dual;

If you need more or less precision (E.G. rounding) than what is above, adjust the date formats (both need to be the same format). For example, this will return 2 months down to the seconds level of precision:

select TO_TIMESTAMP(TO_CHAR(ADD_MONTHS(SYSTIMESTAMP, 2), 
'YYYYMMDD HH24:MI:SS'), 'YYYYMMDD HH24:MI:SS') from dual;

This is the closest I can get (as a character) to the format you need:

select TO_CHAR( 
TO_TIMESTAMP(TO_CHAR(ADD_MONTHS(SYSTIMESTAMP, 2), 
'YYYYMMDD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD HH24:MI:SS.FF TZR') from dual;