DATEDIFF function in Oracle
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 DateDiffFROM dual
Just subtract the two dates:
select date '2000-01-02' - date '2000-01-01' as dateDifffrom 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
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') datedifffrom 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.