I'm attempting to fulfill a rather difficult reporting request from a client, and I need to find away to get the difference between two DateTime columns in minutes. I've attempted to use trunc and round with various formats and can't seem to come up with a combination that makes sense. Is there an elegant way to do this? If not, is there any way to do this?
By default, oracle date subtraction returns a result in # of days.
So just multiply by 24 to get # of hours, and again by 60 for # of minutes.
Example:
select
round((second_date - first_date) * (60 * 24),2) as time_in_minutes
from
(
select
to_date('01/01/2008 01:30:00 PM','mm/dd/yyyy hh:mi:ss am') as first_date
,to_date('01/06/2008 01:35:00 PM','mm/dd/yyyy HH:MI:SS AM') as second_date
from
dual
) test_data
http://asktom.oracle.com/tkyte/Misc/DateDiff.html - link dead as of 2012-01-30
Looks like this is the resource:
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551242712657900129
By default, oracle date subtraction returns a result in # of days.
So just multiply by 24 to get # of hours, and again by 60 for # of minutes.
Example:
select
round((second_date - first_date) * (60 * 24),2) as time_in_minutes
from
(
select
to_date('01/01/2008 01:30:00 PM','mm/dd/yyyy hh:mi:ss am') as first_date
,to_date('01/06/2008 01:35:00 PM','mm/dd/yyyy HH:MI:SS AM') as second_date
from
dual
) test_data
By default, oracle date subtraction returns a result in # of days.
So just multiply by 24 to get # of hours, and again by 60 for # of minutes.
Example:
select
round((second_date - first_date) * (60 * 24),2) as time_in_minutes
from
(
select
to_date('01/01/2008 01:30:00 PM','mm/dd/yyyy hh:mi:ss am') as first_date
,to_date('01/06/2008 01:35:00 PM','mm/dd/yyyy HH:MI:SS AM') as second_date
from
dual
) test_data
http://asktom.oracle.com/tkyte/Misc/DateDiff.html - link dead as of 2012-01-30
Looks like this is the resource:
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551242712657900129
By default, oracle date subtraction returns a result in # of days.
So just multiply by 24 to get # of hours, and again by 60 for # of minutes.
Example:
select
round((second_date - first_date) * (60 * 24),2) as time_in_minutes
from
(
select
to_date('01/01/2008 01:30:00 PM','mm/dd/yyyy hh:mi:ss am') as first_date
,to_date('01/06/2008 01:35:00 PM','mm/dd/yyyy HH:MI:SS AM') as second_date
from
dual
) test_data
Source: Stackoverflow.com