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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

you should divide hours by 24 not 11 like this  select to char sysdate - 2 24   dd-mon-yyyy HH24   from dual

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to date-arithmetic