How to subtract hours from a date in Oracle so it affects the day also

Question

I'm trying to subtract date from Oracle so it even effect the day as well. For example, if the timestamp is 01/June/2015 00 hours and if I subtract 2 hours, I want to be able to go to to 31/May/2014 22 hours.

I tried

to_char(sysdate-(2/11), 'MM-DD-YYYY HH24')

but it only subtracts the hour; it does not touch the day itself.

Answer 1

Try this:

SELECT to_char(sysdate - (2 / 24), 'MM-DD-YYYY HH24') FROM DUAL

To test it using a new date instance:

SELECT to_char(TO_DATE('11/06/2015 00:00','dd/mm/yyyy HH24:MI') - (2 / 24), 'MM-DD-YYYY HH24:MI') FROM DUAL

Output is: 06-10-2015 22:00, which is the previous day.

Answer 2

date - n will subtract n days form given date. In order to subtract hrs you need to convert it into day buy dividing it with 24. In your case it should be to_char(sysdate - (2 + 2/24), 'MM-DD-YYYY HH24'). This will subract 2 days and 2 hrs from sysdate.

Answer 3

sysdate-(2/11)

A day consists of 24 hours. So, to subtract 2 hours from a day you need to divide it by 24:

DATE_value - 2/24

Using interval for the same:

DATE_value - interval '2' hour

Answer 4

Others have commented on the (incorrect) use of 2/11 to specify the desired interval.

I personally however prefer writing things like that using ANSI interval literals which makes reading the query much easier:

sysdate - interval '2' hour

It also has the advantage of being portable, many DBMS support this. Plus I don't have to fire up a calculator to find out how many hours the expression means - I'm pretty bad with mental arithmetics ;)

Answer 5

you should divide hours by 24 not 11
like this:
select to_char(sysdate - 2/24, 'dd-mon-yyyy HH24') from dual