CODEWITHSUNDEEP
sqloracle-databasedate
user1520344
user1520344

Reputation: 17

Calculating difference of dates in Oracle

I need to calculate difference of two dates in Oracle. It's not a problem, but there is a condition. I made a timestamp when the task was created and then new timestamp when the task was finished. And I need to calculate the difference, but:

When the task was created and finished in the same day I need to get the difference = 1 (because they were working on it for one day) and when the task was started yesterday and finished today I need the difference = 2. I hope you understand, if not ask me for more information.

Upvotes: 0

Views: 508

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

I'll assume here that your dates are held in columns of type DATE. If so, the following should do what you want:

SELECT TRUNC(END_DATE) - TRUNC(START_DATE) + 1 AS ELAPSED_DAYS
  FROM A_TABLE

Share and enjoy.

Upvotes: 2

Fahim Parkar
Fahim Parkar

Reputation: 31627

select MOD(24 * (to_date('2009-07-08 23:00', 'YYYY-MM-DD hh24:mi') 
         - to_date('2009-07-07 22:00', 'YYYY-MM-DD hh24:mi')), 24)+1 diff_days 
   from dual;

Demo

Upvotes: 0

user1519979
user1519979

Reputation: 1874

select date2 - date1 + 1 from <tablename>

Upvotes: 1

Related Questions