Calculating difference of dates in Oracle
Oracle | Nauman | November 18, 2009 at 9:28 amHow to calculate difference between two dates in oracle, as subtraction of dates returns numeric value as difference. With some slight changes/enhancements we can track back the subtraction values to some readable date formats:
Here are some examples, these can also be amended for user convenience:
SQL> SELECT floor(((date1-date2)*24*60*60)/3600) 2 || ' HOURS ' || 3 floor((((date1-date2)*24*60*60) - 4 floor(((date1-date2)*24*60*60)/3600)*3600)/60) 5 || ' MINUTES ' || 6 round((((date1-date2)*24*60*60) - 7 floor(((date1-date2)*24*60*60)/3600)*3600 - 8 (floor((((date1-date2)*24*60*60) - 9 floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) )) 10 || ' SECS ' time_difference 11 FROM dates; TIME_DIFFERENCE -------------------------------------------------------------------------------- 24 HOURS 0 MINUTES 0 SECS 1 HOURS 0 MINUTES 0 SECS 0 HOURS 1 MINUTES 0 SECS
SQL> SELECT to_number( to_char(to_date('1','J') +
2 (date1 - date2), 'J') - 1) days,
3 to_char(to_date('00:00:00','HH24:MI:SS') +
4 (date1 - date2), 'HH24:MI:SS') time
5 FROM dates;
DAYS TIME
---------- --------
1 00:00:00
0 01:00:00
0 00:01:00
SQL> select numtodsinterval(date1-date2,'day') time_difference from dates; TIME_DIFFERENCE ---------------------------------------------------------------- +000000001 00:00:00.000000000 +000000000 01:00:00.000000000 +000000000 00:01:00.000000000
SQL> SELECT floor((date1-date2)*24) 2 || ' HOURS ' || 3 mod(floor((date1-date2)*24*60),60) 5 || ' MINUTES ' || 6 mod(floor((date1-date2)*24*60*60),60) 10 || ' SECS ' time_difference 11 FROM dates; TIME_DIFFERENCE -------------------------------------------------------------------------------- 24 HOURS 0 MINUTES 0 SECS 1 HOURS 0 MINUTES 0 SECS 0 HOURS 1 MINUTES 0 SECS
Tags: date, datediff, difference, Oracle, subtraction



Tweet This
Digg This
Save to delicious
Stumble it