CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Ammad Wasim
Ammad Wasim

Reputation: 81

How to extract No. of Days between 2 dates in oracle sql?

I want No. of days between these 2 dates using Oracle SQL

Dates:

BETWEEN "1/1/2018" AND "6/11/2018"

How to write SQL Query?

Upvotes: 0

Views: 9502

Answers (4)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

Just use

select date'2018-11-06' - date'2018-01-01' + 1 as days_difference
  from dual;

DAYS_DIFFERENCE
---------------
     310

or

with t( myDate ) as
(
 select date'2018-11-06' from dual union all 
 select date'2018-01-01' from dual
)
select max(myDate) - min(myDate) + 1 as days_difference
  from t;

DAYS_DIFFERENCE
---------------
     310

Upvotes: 0

Goran Kutlaca
Goran Kutlaca

Reputation: 2024

As other answers have pointed out you can simply divide two dates, but there is also no need for any additional arithmetic.

The code:

select to_date('6/11/2018', 'DD/MM/YYYY') - to_date('1/1/2018', 'DD/MM/YYYY')
  from dual;

The result: 309

Upvotes: 1

Nikhil
Nikhil

Reputation: 3950

you can simple do:

select date1-date2 form dual;

or

select (sysdate-to_date('01-jan-2018'))-(sysdate-to_date('10-jan-2018'))from dual;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143103

between date '2018-01-01' and date '2018-11-06'

where DATE literal looks exactly like that: DATE 'YYYY-MM-DD'

In your example:

  • double quote's can't be used
  • even if you used single quotes, that would be a string, not DATE so you'd depend on whether Oracle is capable of converting it (implicitly) to date or not
  • therefore, always use dates, not strings

[EDIT]

This is how you select the whole calendar between those two dates:

select date '2018-01-01' + level - 1
from dual
connect by level <= date '2018-11-06' - date '2018-01-01' + 1;

Upvotes: 2

Related Questions