In Oracle, how do I subtract a month from timestam

Question

This question already has an answer here:

• Add 2 months to current timestamp 5 answers

In Oracle, how do I subtract a month from timestamp retaining the millisecond part.

I am using add_months function but it is truncating the milliseconds. Is there a built in function or I have to manually extract month part of timestamp and add to it?

Answer 1

You can get it done by using year to month interval literal:

clear screen;

select current_timestamp                      as res_1
     , current_timestamp - interval '1' month as res_2
  from dual

Result:

RES_1                          RES_2                                
------------------------------ -----------------------------
05-FEB-14 02.02.32.383089000   05-JAN-14 02.02.32.383089000 

When you use add_months() function, data type conversion takes place - the value of timestamp data type becomes a value of date data type, which does not have fractional seconds.

As @jonearles absolutely correctly pointed out in a comment to the answer, using presented above method to subtract one or more months from a given value of timestamp data type preserving milliseconds, may cause ORA-01839: date not valid for month specified.

To prevent that error from popping up, the query can be rewritten as follows:

clear screen;

with t1(col) aS(
  select current_timestamp                     from dual union all
  select timestamp '2000-03-30 12:12:12.123'   from dual union all
  select timestamp '2014-03-30 01:12:59.64567' from dual 
)
select col as cur                                    
     , cast(add_months(col, -1) as timestamp) + (col - cast(col as date)) as prev
  from t1

Result:

CUR                                    PREV                          
-------------------------------------- -------------------------------
14-FEB-14 01.45.46.344187000 PM        14-JAN-14 01.45.46.344187000 PM  
30-MAR-00 12.12.12.123000000 PM        29-FEB-00 12.12.12.123000000 PM 
30-MAR-14 01.12.59.645670000 AM        28-FEB-14 01.12.59.645670000 AM