CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
hi4ppl
hi4ppl

Reputation: 625

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

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.

Upvotes: 33

Views: 180954

Answers (5)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

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

Upvotes: 6

adel sameer
adel sameer

Reputation: 53

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

Upvotes: 1

user330315
user330315

Reputation:

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 ;)

Upvotes: 81

Tapan Pandya
Tapan Pandya

Reputation: 114

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.

Upvotes: 1

m3nation
m3nation

Reputation: 206

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.

Upvotes: 11

Related Questions