DATEDIFF function in Oracle
SqlOracleSelectDatediffSql Problem Overview
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;
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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:
https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i48042
Solution 3 - Sql
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.
Solution 4 - Sql
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;