31

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.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
hi4ppl
  • 595
  • 1
  • 6
  • 21
  • 1
    What makes you think `-(2/11)` subtracts 2 hours? It is not an obvious notation. It looks like it might subtract `0.22` units of some sort — I reserve judgement on whether that's days or seconds or some other unit. – Jonathan Leffler Jun 11 '15 at 05:44
  • 4
    @JonathanLeffler: in Oracle an expression `date_value - integer` always means `date_value - number of days`. Fractional values are interpreted as hours. However 2/11 is a pretty obfuscated way of defining the expected value. I personally always use ANSI interval literals for something like that: `sysdate - interval '2' hour` –  Jun 11 '15 at 06:01
  • 1
    @a_horse_with_no_name: Thanks — that makes sense. The basic unit of an Oracle DATE is days and time is fractions of a day. My memory is rotten; 2/11 = 0.1818181818… and 2/9 = 0.2222222222… It still doesn't seem that 2/11 is a way to specify 2 hours even so (it is somewhat over 4 hours). – Jonathan Leffler Jun 11 '15 at 06:37

5 Answers5

73

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

11

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.

m3nation
  • 206
  • 2
  • 7
6

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
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
1

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.

Tapan Pandya
  • 114
  • 2
1

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

adel sameer
  • 43
  • 1
  • 9