DB version : 11.2.0.4
-- Below query gives current time and the time 23 hours back
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select sysdate, (sysdate -1) - interval '1' hour from dual;
SYSDATE (SYSDATE-1)-INTERVAL
-------------------- --------------------
09-MAR-2017 22:10:31 08-MAR-2017 21:10:31
I want to subtract these two dates and convert into hours. But, I am getting the below error
SQL> select sysdate - ( (sysdate -1) - interval '1' hour)* 24 from dual;
select sysdate - ( (sysdate -1) - interval '1' hour)* 24 from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
Any idea how I can achieve this ? In my real life scenario , I just want to find hour difference from the current time to a business column
Something like
select order_id,
cust_id,
order_date,
sysdate - order_date --- (Hours since the order has been placed )
.
.
from order_master;