SQL: how to subtract TIME datatype from two different quantities and produce AS sum in HH: MM: SS
I have a table (TimeLog) that has 2 columns (StartTime, TIME (0), NOT NULL) and (FinishTime, TIME (0), NOT NULL) I want to write a query that subtracts (FinishTime - StartTime) AS ElapsedTime and gives the amount in HH: MM: SS
I tried
SELECT StartTime
,FinishTime
,REPLACE(CAST(CAST(DATEDIFF(Hour, StartTime, FinishTime )AS VARCHAR (2)) + ':' + CAST(DATEDIFF(MINUTE, StartTime, FinishTime )AS VARCHAR(2)) + ':'
+ Cast(DATEDIFF(Second, StartTime, FinishTime )AS VARCHAR (2)) AS VARCHAR(8)),'*','00') As ElapsedTime
FROM TimeLog
StartTime FinishTime ElapsedTime
08:00:00 08:25:00 0:25:00
08:25:00 09:15:00 1:50:00
09:55:00 12:32:00 3:00:00
12:32:00 14:31:00 2:00:00
12:32:00 13:55:00 1:83:00
09:55:00 11:42:00 2:00:00
07:30:00 08:45:00 1:75:00
07:00:00 07:15:00 0:15:00
07:15:00 08:10:00 1:55:00
But this will only work if ElapsedTime is less than an hour. If more than 1 hour has passed, the result will be in the wrong format as I want. Can anyone please help?
source to share
You can add seconds from datediff()
to date 0
using dateadd()
and convert()
to data type time(0)
, for example:
select
StartTime
, FinishTime
, ElapsedTime = convert(time(0),dateadd(second,datediff(second,StartTime,FinishTime),0))
/* for greater than 24 hours: */
, ElapsedTime = right('0' + convert(varchar(9),(datediff(second,StartTime,FinishTime) / 3600 )),2) + ':'
+ right('0' + convert(varchar(2),(datediff(second,StartTime,FinishTime) / 60) % 60 ),2) + ':'
+ right('0' + convert(varchar(2),(datediff(second,StartTime,FinishTime) % 60 )),2)
from TimeLog
Demo version of the rexter: http://rextester.com/KVC7988
returns:
+-----------+------------+-------------+
| StartTime | FinishTime | ElapsedTime |
+-----------+------------+-------------+
| 08:00:00 | 08:25:00 | 00:25:00 |
| 08:25:00 | 09:15:00 | 00:50:00 |
| 09:55:00 | 12:32:00 | 02:37:00 |
| 12:32:00 | 14:31:00 | 01:59:00 |
| 12:32:00 | 13:55:00 | 01:23:00 |
| 09:55:00 | 11:42:00 | 01:47:00 |
| 07:30:00 | 08:45:00 | 01:15:00 |
| 07:00:00 | 07:15:00 | 00:15:00 |
| 07:15:00 | 08:10:00 | 00:55:00 |
+-----------+------------+-------------+
I believe you may have been confused about how it works datediff()
. datediff(second,StartTime,FinishTime)
returns the number of seconds between two moments, it does not compare the numbers in seconds each time.
source to share