Calculate time difference in SQL-CodePudding

We have two columns in SQL. one is total_work_time & next is total_exeption_time & both column data type is varchar

total_work_time value is 07:15:00

total_exeption_time value is 01:15:00

So I need to subtract total_work_time - total_exeption_time and the result will be 06:00:00.

I have tried with concat(DATEDIFF(HOUR,total_exeption_time,total_work_time),':', DATEDIFF(MINUTE,total_exeption_time,total_work_time))

But the result is 6:360. from this, 360 is the problem, it taken total minutes. I need the result structure like 06:00:00. How to fix this issue using SQL Server.

CodePudding user response：

You should be storing time values in a TIME datatype - using the correct datatype is not only a best practice but will reduce the problems you face in future.

You can convert your VARCHAR values to TIME and then use the following calculation which takes the difference in seconds (your lowest unit of interest one assumes) and creates a new TIME result.

DECLARE @total_work_time TIME = '07:15:00', @total_exeption_time TIME = '01:15:00';

SELECT CONVERT(TIME, DATEADD(SECOND, DATEDIFF(SECOND, @total_exeption_time, @total_work_time), '00:00'));