I need to use Oracle but DATEDIFF function doesn't work in Oracle DB.
How to write the following code in Oracle? I saw some examples using INTERVAL or TRUNC.
SELECT DATEDIFF ('2000-01-01','2000-01-02') AS DateDiff; 4 Answers
In Oracle, you can simply subtract two dates and get the difference in days. Also note that unlike SQL Server or MySQL, in Oracle you cannot perform a select statement without a from clause. One way around this is to use the builtin dummy table, dual:
SELECT TO_DATE('2000-01-02', 'YYYY-MM-DD') - TO_DATE('2000-01-01', 'YYYY-MM-DD') AS DateDiff FROM dual Just subtract the two dates:
select date '2000-01-02' - date '2000-01-01' as dateDiff from dual; The result will be the difference in days.
More details are in the manual:
You can simply subtract two dates. You have to cast it first, using to_date:
select to_date('2000-01-01', 'yyyy-MM-dd') - to_date('2000-01-02', 'yyyy-MM-dd') datediff from dual ; The result is in days, to the difference of these two dates is -1 (you could swap the two dates if you like). If you like to have it in hours, just multiply the result with 24.
We can directly subtract dates to get difference in Days.
SET SERVEROUTPUT ON ; DECLARE V_VAR NUMBER; BEGIN V_VAR:=TO_DATE('2000-01-02', 'YYYY-MM-DD') - TO_DATE('2000-01-01', 'YYYY-MM-DD') ; DBMS_OUTPUT.PUT_LINE(V_VAR); END; 
