How to calculate difference between two dates in oracle 11g SQL

Question

When I am trying to calculate the date difference by using datediff function it showing that invalid identifier     SELECT DATEDIFF day  2008-08-05   2008-06-05   AS DiffDate from da static trade   Error   invalid identifier    Can you please tell me what is the function to calculate date difference

User · Answer

basically the to char sysdate  DDD   returns no of days from  1-jan-yyyy to sysdate  so that if subtract two dates it will return that you will get difference between two dates  select to char sysdate  DDD   -to char to date  19-08-1995   dd-mm-yyyy    DDD   from dual

User · Answer

You can not use DATEDIFF but you can use this  if columns are not date type    SELECT  to date  2008-08-05   YYYY-MM-DD  -to date  2008-06-05   YYYY-MM-DD    AS DiffDate from dual   you can see the sample  http   sqlfiddle com   4 d41d8 34609

User · Answer

Oracle DateDiff is from a different product  probably mysql  which is now owned by Oracle      The difference between two dates  in oracle s usual database product  is in days  which can have fractional parts    Factor by 24 to get hours  24 60 to get minutes  24 60 60 to get seconds  that s as small as dates go    The math is 100  accurate for dates within a couple of hundred years or so  E g  to get the date one second before midnight of today  you could say  select trunc sysdate  - 1 24 60 60 from dual   That means  the time right now   truncated to be just the date  i e  the midnight that occurred this morning    Then it subtracts a number which is the fraction of 1 day that measures one second   That gives you the date from the previous day with the time component of 23 59 59

User · Answer

There is no DATEDIFF   function in Oracle  On Oracle  it is an arithmetic issue  select DATE1-DATE2 from table

User · Answer

Oracle support Mathematical Subtract - operator on Data datatype  You may directly put in select clause following statement   to char  s last upd     s created     999999D99     Check the EXAMPLE for more visibility   In case you need the output in termes of hours  then the below might help   Select to number substr numtodsinterval  END TIME - START TIME      day    2 9   24   to number substr numtodsinterval  END TIME - START TIME     day     12 2            to number substr numtodsinterval  END TIME - START TIME     day     15 2    from  TABLE NAME

User · Answer

You can use this   SET FEEDBACK OFF  SET SERVEROUTPUT ON   DECLARE   V START DATE  CHAR 17      28 03 16 17 20 00     V END DATE    CHAR 17      30 03 16 17 50 10     V DATE DIFF   VARCHAR2 17    BEGIN  SELECT    TO NUMBER  SUBSTR NUMTODSINTERVAL TO DATE V END DATE    DD MM YY HH24 MI SS   - TO DATE V START DATE   DD MM YY HH24 MI SS     DAY    02  9     24       TO NUMBER  SUBSTR NUMTODSINTERVAL TO DATE V END DATE    DD MM YY HH24 MI SS   - TO DATE V START DATE   DD MM YY HH24 MI SS     DAY    12  2                       SUBSTR NUMTODSINTERVAL TO DATE V END DATE    DD MM YY HH24 MI SS   - TO DATE V START DATE   DD MM YY HH24 MI SS     DAY    14  6  AS  HH24 MI SS    INTO V DATE DIFF FROM    DUAL   DBMS OUTPUT PUT LINE V DATE DIFF   END

[sql] How to calculate difference between two dates in oracle 11g SQL

Examples related to sql

Examples related to oracle11g