Search This Blog

Saturday 11 March 2017

How can I get the number of days between 2 dates in Oracle?

when subtracting two dates in Oracle By default its return number of days.

--Passing one date parameter and subtracting from current date
select TRUNC(sysdate) - to_date('1/3/2017''dd/MM/yyyy'AS DiffDate
  from dual;

--Passing both date parameter and subtracting lower date from greater date
SELECT to_date('2017-03-06''YYYY-MM-DD') -
       to_date('2017-03-01''YYYY-MM-DD'AS DiffDate
  from dual;

--To get the number of days different in SQL statement
SELECT TRUNC(sysdate) -
       to_date(to_char(created, 'dd/MM/yyyy'), 'dd/MM/yyyy'As Datediff
  FROM dba_objects
 WHERE object_name = 'TABLE_NAME'
   AND owner = 'TABLE_OWNER'
   AND object_type = 'TABLE';

No comments:

Post a Comment