This content has been marked as final. Show 6 replies
FLOOR((date1 - date2) * 24 * 60) minutes
Here are some more date difference related queries
To retrieve the total elapsed time in minutes, you can execute the following SQL:
select (endingDateTime - startingDateTime) * 1440
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
select 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
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
the o/p is
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
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>
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