6 Replies Latest reply on Jan 4, 2011 11:06 AM by Ora

# dates differences in minutes

i have two dates.i need the difference of teh two dates in minutes.how to do it.
• ###### 1. Re: dates differences in minutes
try

SELECT
FLOOR((date1 - date2) * 24 * 60) minutes
FROM
dual

Nimish Garg
http://nimishgarg.blogspot.com/
• ###### 2. Re: dates differences in minutes
Here are some more date difference related queries

http://nimishgarg.blogspot.com/2010/08/oracle-sql-date-difference-in-days.html

Nimish Garg
http://nimishgarg.blogspot.com/
• ###### 3. Re: dates differences in minutes
To retrieve the total elapsed time in minutes, you can execute the following SQL:

select (endingDateTime - startingDateTime) * 1440
from table_name;

Since taking the difference between two dates in Oracle returns the difference in fractional days, you need to multiply the result by 1440 to translate your result into elapsed minutes.

24 * 60 = 1440

24 hours in a day * 60 minutes in an hour
• ###### 4. Re: dates differences in minutes
select min(CREATION_DATE) ,max(CREATION_DATE)
from xxcss_macd_detail_intf

o/p is
MIN(CREATION_DATE)     MAX(CREATION_DATE)
10/22/2010 12:22:42 AM     10/22/2010 5:32:49 AM

select (numtodsinterval(max(CREATION_DATE)-min(CREATION_DATE),'day') ) timedif
from xxcss_macd_detail_intf

o/p is

TIMEDIF
+00 05:10:06.999999

i wanto to convert this 5 hours 10 minutes into minutes
i am using the below query

select (numtodsinterval(max(CREATION_DATE)-min(CREATION_DATE),'day') ) *1440 timedif_min
from xxcss_macd_detail_intf

the o/p is
\
TIMEDIF_MIN
+310 02:47:59.999998

but i want to get only 310 with out + symbol and removing of this value 02:47:59.999998.

i have to get only 310.how to do it
• ###### 5. Re: dates differences in minutes
Try this:
``````SQL> ed
Wrote file afiedt.buf

1  with t as (select SYSDATE col1, SYSDATE - 10 col FROM dual)
2  SELECT n_days * 24 * 60 In_Mins FROM
3* (SELECT col1-col n_days from t)
SQL> /

IN_MINS
----------
14400

SQL> ``````
• ###### 6. Re: dates differences in minutes
As earlier I explained, difference between two dates in Oracle returns the difference in fractional days, you need to multiply the result by 1440 to translate your result into elapsed minutes. Also there will not be any problem of '+' symbol.

with t1 as (select min(CREATION_DATE) a,max(CREATION_DATE) b
from xxcss_macd_detail_intf