Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2006
    Posts
    83

    DATEDIFF function in Oracle

    Hi ,

    I am having a SQL Server query with DATEDIFF function in it .

    PHP Code:
    SELECT DATEDIFF(ddmydate GETDATE()) 
    This query will extract the difference between the days of mydate and GETDATE() values .
    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

    SELECT ROUND(( (SYSDATE) - mydate ))
    But of no use .

    Kindly suggest me the equivalent for the SQL Server DATEDIFF function in Oracle

    Thanks

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,133
    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.

    Alan

  3. #3
    Join Date
    Oct 2006
    Posts
    83

    Lightbulb

    The above Query will return an intervel .

    I tried executing the below query where i have the datediff function in a where condition .

    select tem1 from tabtemp where ROUND((SYSDATE) - mydate) = 0;
    The Oracle throws the following error

    ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL

    Kindly suggest me

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,133
    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

    where mydate>(systimestamp-1);

    Alan

  5. #5
    Join Date
    Oct 2014
    Posts
    7
    A great*exchange of information.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •