DATEDIFF function in Oracle

sql oracle select datediff
user3601310 picture user3601310 · Feb 9, 2015 · Viewed 371.2k times · Source

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;

Answer

Mureinik picture Mureinik · Feb 9, 2015

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

Related questions

If statement in select (ORACLE)

Hi I have simply select and works great: select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity, gcrs.Area_name, gcrs.sector_name, substr(gcrs.stream_…

Read More
How to kill a running SELECT statement

How can I stop a running SELECT statement by killing the session? The command is continuously giving me output based on the SELECT statement, I want to stop it in between.

Read More
Creating new table with SELECT INTO in SQL

Possible Duplicate: SELECT INTO using Oracle I have came across SQL SELECT INTO statement for creating new table and also dumping old table records into new table in single SQL statement as SELECT * INTO NEW_TABLE FROM OLD_TABLE; But …

Read More