How to subtract hours from a date in Oracle so it affects the day also
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 ;)
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.
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