Time difference with hours, minutes and seconds in SQL Server

gupuwyp2 于 8个月前发布在 SQL Server

关注(0)|答案(3)|浏览(93)

I need to find time difference between two columns with hours, minutes and seconds.

These are two datetime columns in my table:

STOP_TIME                      Start_Time
------------------------------------------------------
2016-05-10 03:31:00.000        2016-05-10 02:25:34.000

I calculated second difference for stoptime and starttime. 3926 is the second difference.

I need to convert this to time format hh:mm:ss .

sql-server

来源：https://stackoverflow.com/questions/41422539/time-difference-with-hours-minutes-and-seconds-in-sql-server

3条答案

按热度按时间

pgx2nnw81#

This should work for you -

DECLARE @STOP_TIME DATETIME = '2016-05-10 03:31:00.000',
        @Start_Time DATETIME = '2016-05-10 02:25:34.000'
SELECT 
     RIGHT('0' + CAST(DATEDIFF(S, @Start_Time, @STOP_TIME) / 3600 AS VARCHAR(2)),2) + ':'
   + RIGHT('0' + CAST(DATEDIFF(S, @Start_Time, @STOP_TIME) % 3600/60 AS VARCHAR(2)),2) + ':'
   + RIGHT('0' + CAST(DATEDIFF(S, @Start_Time, @STOP_TIME) % 60 AS VARCHAR(2)),2)

赞(0）回复(0）举报 8个月前

jqjz2hbq2#

Sql server supports adding and subtracting on Datetime data type, so you can simply do something like this:

DECLARE @StartTime datetime = '2016-05-10 02:25:34.000',
        @EndTime datetime = '2016-05-10 03:31:00.000'

SELECT CAST(@EndTime - @StartTime as Time) As TimeDifference

Result: 01:05:26

Note: As TT rightfully wrote in his comment, casting to time will only work if the difference between @EndTime and @StartTime is less then 24 hours. If you need to compare times that are further apart, you need to use one of the other solutions suggested.

赞(0）回复(0）举报 8个月前

7rtdyuoh3#

This should work for any cases:

DECLARE @STOP_TIME DATETIME = '2016-05-10 05:27:35.000',
    @Start_Time DATETIME = '2016-05-10 02:25:34.000' 
SELECT CONCAT(format(DATEDIFF(SECOND, @Start_Time, @STOP_TIME)/3600,'00'),':'
          ,format(DATEDIFF(SECOND, @Start_Time, @STOP_TIME)%3600/60,'00'),':'
          ,format(DATEDIFF(SECOND, @Start_Time, @STOP_TIME)%3600%60,'00')
         ) AS 'hrs:mins:secnds'

赞(0）回复(0）举报 8个月前

我来回答

Time difference with hours, minutes and seconds in SQL Server

3条答案

相关问题

热门标签

最新问答