How to Get the Number of Days Between 2 Dates in Oracle 11G

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

I figured it out myself. I need

select extract(day from sysdate - to_date('2009-10-01', 'yyyy-mm-dd')) from dual

how to calculate days between two dates in oracle?

you can try this: (sysdate is already date format so you can use like this)

  select trunc(sysdate) -to_date('03/09/2010','MM/DD/YYYY') "Days" from dual;

Get the number of days between two dates in Oracle, inclusive of the dates

In Oracle substracting two dates returns the number of days between two dates.

A minus operator works in the same way as for numbers:

20 - 20 = 0   ===>      2013-05-20  -  2013-05-20 = 0
25 - 20 = 5   ===>      2013-05-25  -  2013-05-20 = 5

If you want to include last number or last date, you need to add 1:

20 - 20 + 1 = 1   ===>      2013-05-20  -  2013-05-20  + 1 = 1
25 - 20 + 1 = 6   ===>      2013-05-25  -  2013-05-20  + 1 = 6

DATEDIFF function in Oracle

In Oracle, you can simply subtract two dates and get the difference in days. Also note that unlike SQL Server or MySQL, in Oracle you cannot perform a select statement without a from clause. One way around this is to use the builtin dummy table, dual:

SELECT TO_DATE('2000-01-02', 'YYYY-MM-DD') -  
       TO_DATE('2000-01-01', 'YYYY-MM-DD') AS DateDiff
FROM   dual

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

There is no DATEDIFF() function in Oracle. On Oracle, it is an arithmetic issue

select DATE1-DATE2 from table 

Oracle SQL - get number of days between two dates for a specified month

You can expand each date range into its component days, either with a hierarchical query or a recursive CTE; and then count how many match the specified month:

with r (datefrom, dateto, dateday) as (
  select datefrom, dateto, datefrom
  from t
  union all select datefrom, dateto, dateday + 1
  from r
  where dateday < dateto
)
select datefrom, dateto,
  to_char(to_date(:monthnum, 'MM'), 'MON', 'NLS_DATE_LANGUAGE=ENGLISH') as month,
  count(case when to_char(dateday, 'MM') = :monthnum then dateday end) as numofdays
from r
group by datefrom, dateto
order by datefrom, dateto;

Using 1, 2 and 3 in turn for the bind variable generates:

DATEFROM  DATETO    MONTH         NUMOFDAYS
--------- --------- ------------ ----------
20-JAN-16 10-MAR-16 JAN                  12
28-JAN-16 15-FEB-16 JAN                   4
05-FEB-16 16-FEB-16 JAN                   0
10-FEB-16 03-MAR-16 JAN                   0

DATEFROM  DATETO    MONTH         NUMOFDAYS
--------- --------- ------------ ----------
20-JAN-16 10-MAR-16 FEB                  29
28-JAN-16 15-FEB-16 FEB                  15
05-FEB-16 16-FEB-16 FEB                  12
10-FEB-16 03-MAR-16 FEB                  20

DATEFROM  DATETO    MONTH         NUMOFDAYS
--------- --------- ------------ ----------
20-JAN-16 10-MAR-16 MAR                  10
28-JAN-16 15-FEB-16 MAR                   0
05-FEB-16 16-FEB-16 MAR                   0
10-FEB-16 03-MAR-16 MAR                   3

Alternatively you can generate all the days for the specified month and get the intersect of both sets.

As you're only specifying the month, not the year, you're assuming a range will never span more than a year - or if it does, that you want to count days in the months that appear in both years.

It's also assuming the same date range doesn't appear more than once; if it does then the grouping and counting will be off. Presumably your real data has a key that you haven't shown, which could be included in the CTE and the group-by clause to avoid that.

---

Related Topics