Menu
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.
778
In Oracle, when you add or subtract a number to a date, the unit is days. So SYSDATE + 1 adds 1 day. To add some other increment, you just multiply or divide appropriately, i.e. SYSDATE - 1/24 subtracts an hour, SYSDATE - 1/24/60 subtracts a minute, etc.
To calculate the difference between the timestamps in Oracle, simply subtract the start timestamp from the end timestamp (here: arrival - departure ). The resulting column will be in INTERVAL DAY TO SECOND . The first number you see is the number of whole days that passed from departure to arrival .
In Oracle, you can subtract any number of days simply by subtracting that number from the current date. Here, since you need to subtract one day, you use current_date - 1 . Then you use the TO_DATE() function to cast the result to the column type date .
You can use the TO_DATE function. Convert both to timestamp and compare. This solution handles nulls as greatest will return null if one of the params is null.
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 ;)
150
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.
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
