DATEDIFF function in Oracle like SQL Server

[simona007]

Hi guys, hope everyone is good

Can someone tell me the equivalent for the SQL Server DATEDIFF function in Oracle

For example, in Microsoft SQl server, I can do

SELECT DATEDIFF(dd, date_var , GETDATE())

This gives the difference in number of days between date_var and GETDATE(). Now what's the equivalent in Oracle. If i do

select date_var-sysdate from temp

, it gives me an error ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL, what am i missing here?

Regards,
Simona

[tyro]

did u say "got interval", try this

select (SYSTIMESTAMP - date_var) from temp

[rajavu]

First all , you didn't mentioned about the data type of date_var.

If it is non-date datatype , that needs to be converted to date while doing such operations.

SQL> SELECT SYSDATE - TO_DATE('20081205','YYYYMMDD') datediff FROM DUAL;
 
  DATEDIFF
----------
80.4018519
 
 SQL> SELECT TRUNC(SYSDATE) - TO_DATE('20081205','YYYYMMDD') datediff FROM DUAL;
 
  DATEDIFF
----------
        80
 
 SQL>