Count time difference in hh:mm:ss between two Date in SQL Server

Last Reply on Apr 27, 2017 04:53 AM By dharmendr

Posted on Apr 26, 2017 11:04 PM

I have Database with name "parking"

with table A contain field

id (identity)

vehicle

start

end

I want add sum start and end with format (like 2 hour 20 minute)

with store procedure sql server ..

 

thanks

 

any help

 

Posted on Apr 27, 2017 04:53 AM Modified on on Apr 27, 2017 05:01 AM

Hi Ruben12345,

Refer the below sample query.

DECLARE @TABLEA AS TABLE (ID INT IDENTITY(1,1),Vehicle VARCHAR(50),Start DATETIME, [End] DATETIME)
INSERT INTO @TABLEA (Vehicle,Start,[End]) VALUES('Maruti SX4','2017-04-25 11:29:00','2017-04-25 15:00:36')
INSERT INTO @TABLEA (Vehicle,Start,[End]) VALUES('Honda City','2017-04-26 12:29:36','2017-04-26 15:00:36')

SELECT ID,Vehicle,CONVERT(VARCHAR(5),DATEDIFF(S, Start, [End])/3600)+' hours '
+CONVERT(VARCHAR(5),DATEDIFF(S, Start, [End])%3600/60)+' minutes '
+CONVERT(VARCHAR(5),(DATEDIFF(S, Start, [End])%60))+' seconds' AS [TIME] FROM @TABLEA

Output

ID Vehicle TIME
1 Maruti SX4 3 hours 31 minutes 36 seconds
2 Honda City 2 hours 31 minutes 0 seconds