How to calculate the difference between two dates in oracle 11g SQL

Question

When I try to calculate the date difference using the latiff function, it shows this invalid id.

SELECT DATEDIFF(day,'2008-08-05','2008-06-05') AS DiffDate from da_static_trade. Error : invalid identifier. 

Could you tell me what is the function of calculating race.

Answer 1

There is no DATEDIFF() function in Oracle. In Oracle, this is an arithmetic problem

 select DATE1-DATE2 from table 

Answer 2

Oracle Support Mathematical Subtraction - Operator by Data Data Type. You can directly put the following statement in the select clause:

 to_char (s.last_upd – s.created, '999999D99′) 

Check EXAMPLE for more visibility.

If you need a conclusion in terms of hours, then below may help;

 Select to_number(substr(numtodsinterval([END_TIME]-[START_TIME]),'day',2,9))*24 + to_number(substr(numtodsinterval([END_TIME]-[START_TIME],'day'),12,2)) ||':'||to_number(substr(numtodsinterval([END_TIME]-[START_TIME],'day'),15,2)) from [TABLE_NAME]; 

Answer 3

You cannot use DATEDIFF but you can use this (if the columns are not date type):

 SELECT to_date('2008-08-05','YYYY-MM-DD')-to_date('2008-06-05','YYYY-MM-DD') AS DiffDate from dual 

you can see the sample

http://sqlfiddle.com/#!4/d41d8/34609

Answer 4

You can use this:

 SET FEEDBACK OFF; SET SERVEROUTPUT ON; DECLARE V_START_DATE CHAR(17) := '28/03/16 17:20:00'; V_END_DATE CHAR(17) := '30/03/16 17:50:10'; V_DATE_DIFF VARCHAR2(17); BEGIN SELECT (TO_NUMBER( SUBSTR(NUMTODSINTERVAL(TO_DATE(V_END_DATE , 'DD/MM/YY HH24:MI:SS') - TO_DATE(V_START_DATE, 'DD/MM/YY HH24:MI:SS'), 'DAY'), 02, 9)) * 24) + (TO_NUMBER( SUBSTR(NUMTODSINTERVAL(TO_DATE(V_END_DATE , 'DD/MM/YY HH24:MI:SS') - TO_DATE(V_START_DATE, 'DD/MM/YY HH24:MI:SS'), 'DAY'), 12, 2))) || SUBSTR(NUMTODSINTERVAL(TO_DATE(V_END_DATE , 'DD/MM/YY HH24:MI:SS') - TO_DATE(V_START_DATE, 'DD/MM/YY HH24:MI:SS'), 'DAY'), 14, 6) AS "HH24:MI:SS" INTO V_DATE_DIFF FROM DUAL; DBMS_OUTPUT.PUT_LINE(V_DATE_DIFF); END; 

Answer 5

Oracle DateDiff is another product, possibly mysql (which now belongs to Oracle).

The difference between the two dates (in a regular oracle database product) is days (which may have fractional parts). A factor of 24 to get hours, 24 * 60 to get minutes, 24 * 60 * 60 to get seconds (which are no less than dates). The math is 100% accurate for dates for several hundred years or so. For example. to get the date one second before midnight today, you could say

select trunc (sysdate) - 1/24/60/60 from dual;

This means “time right now,” truncated to be just a date (i.e. midnight that happened this morning). Then he subtracts a number that is 1 day, which measures one second. This gives you the date from the previous day with the 23:59:59 time component.