var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
DATEDIFF function in Oracle
I am having a SQL Server query with DATEDIFF function in it .
This query will extract the difference between the days of mydate and GETDATE() values .
SELECT DATEDIFF(dd, mydate , GETDATE())
Refer below link for DATEDIFF function on SQL Server
I tried to convert the query to Oracle and execute it .
But there is no DATEDIFF function in Oracle .
I tried using ROUND function like
But of no use .
SELECT ROUND(( (SYSDATE) - mydate ))
Kindly suggest me the equivalent for the SQL Server DATEDIFF function in Oracle
The nice thing about Oracle Dates is that they are held as real numbers where 1 represents a day. So your minus should work, did you forget to select from dual?
So difference in days is
select SYSDATE-to_date('01-jan-2007','dd-mon-yyyy') from dual
returns 165.35.... Use round() to round to the nearest day or trunc() to round down to the number of whole days.
If you want it in hours multiply the result by 24, minutes then 1440, seconds then 86400.
The above Query will return an intervel .
I tried executing the below query where i have the datediff function in a where condition .
The Oracle throws the following error
select tem1 from tabtemp where ROUND((SYSDATE) - mydate) = 0;
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL
Kindly suggest me
OK, if your using a timestamp field instead of a date field then you can do the following
where (SYSTIMESTAMP - mydate)<numtodsinterval(1,'day');
however this is probably better in terms of performance