How to get the time difference between two date columns? Oracle & SQL Server

How to get the time difference between two date columns? Oracle & SQL Server Oracle allows two date values to be subtracted from each other returning a numeric value indicating the number of days between the two dates (may be a fraction). This example will show how to relate it back to a time value. Test Data: SQL> CREATE TABLE dates (date1 DATE, date2 DATE); Table created. SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1); 1 row created. SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24); 1 row created. SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24); 1 row created. SQL> SELECT (date1 - date2) FROM dates; DATE1-DATE2 ----------------------- 1 .041666667 .000694444 Solution 1 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 Solution 2 If you don't want to go through the floor and ceiling maths, try this method: 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 Solution 3 Here is a simpler method: SQL> SELECT trunc(date1-date2) days, 2 to_char(trunc(sysdate) + (date1 - date2), 'HH24 "Hours" MI "Minutes" SS "Seconds"') time 3 FROM dates; DAYS TIME ---------- ------------------------------ 1 00 Hours 00 Minutes 00 Seconds 0 01 Hours 00 Minutes 00 Seconds 0 00 Hours 01 Minutes 00 Seconds How to add a day/hour/minute/second to a date value? Oracle The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples: SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual; SYSDATE SYSDATE+1/24 SYSDATE+1/1440 SYSDATE+1/86400 -------------------- -------------------- -------------------- -------------------- 03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13 SQL server: DATEDIFF: This Function has been used to get the difference between two dates Reference: Oracle: http://www.orafaq.com/faq/how_does_one_get_the_time_difference_between_two_date_columns SQL Server : http://www.java2s.com/Code/SQLServer/Date-Timezone/DATEDIFFreturnthedifferencebetweentwodates.htm