1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

DATEDIFF function in Oracle like SQL Server

Discussion in 'SQL PL/SQL' started by simona007, Feb 22, 2009.

  1. simona007

    simona007 Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    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
    Code (Text):

    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
    Code (Text):
    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
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    did u say "got interval", try this
    Code (Text):

    select (SYSTIMESTAMP - date_var) from temp
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    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.

    Code (Text):

    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>