9

I want to subtract "X" days and "X" minutes from sysdate, where the days and minutes are an integer as input parameter. For instance, 10 days and 5 minutes.

I found many examples to subtract either minutes or hours but not a combination of days and minutes.

select sysdate - 5 / 24 / 60 from dual -- will retrieve sysdate minus 5 minutes. 
--What about the days?

Thank you!

Carlos
  • 456
  • 2
  • 7
  • 21

2 Answers2

22

Use an interval literal:

SELECT SYSDATE - INTERVAL '10 00:05' DAY(2) TO MINUTE
FROM   DUAL

Or:

SELECT SYSDATE - INTERVAL '10' DAY - INTERVAL '5' MINUTE
FROM   DUAL

Or just use arithmetic:

SELECT SYSDATE - 10 /* Days */ - 5 / 24 /60 /* Minutes */
FROM   DUAL

Or use NUMTODSINTERVAL:

SELECT SYSDATE - NUMTODSINTERVAL( 10, 'DAY' ) - NUMTODSINTERVAL( 5, 'MINUTE' )
FROM   DUAL
MT0
  • 143,790
  • 11
  • 59
  • 117
1

You can use Interval day to minute - http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#i38598

select sysdate - interval '1 00:05' day to minute from dual
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76