CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2sql-server-2012
Dinesh Reddy Alla
Dinesh Reddy Alla

Reputation: 1717

Time Difference between two dates in Minutes and hours like 1hr 20Min in SQL Server 2012

I need to calculate job time between two dates i.e. I am having table with name job and in that table i am having jobStartDate and JobCompleteDate as Datetime fields now i need to get time duration between those 2 dates i.e.

AS Mentioned by J.D my question is not duplicate I Verified this answer: SQL time difference between two dates result in hh:mm:ss

My Query

SELECT DATEDIFF(HOUR,jobStartDate,JobCompleteDate) FROM tbl_Jobs

My result

1

Expected Output

1:20 Hr

Upvotes: 1

Views: 14708

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Here is an example:

DECLARE @sd DATETIME = '2015-11-03 10:45:35.747'
DECLARE @ed DATETIME = '2015-11-03 15:20:35.747'

SELECT CAST(DATEDIFF(ss, @sd, @ed) / 3600 AS VARCHAR(10)) + ':' +
       CAST((DATEDIFF(ss, @sd, @ed) -  3600 * (DATEDIFF(ss, @sd, @ed) / 3600)) / 60 AS VARCHAR(10)) + ' Hr'

Output:

4:35 Hr

Upvotes: 4

MusicLovingIndianGirl
MusicLovingIndianGirl

Reputation: 5947

You can use CAST

Select CAST((@jobEndDate-@jobStartDate) as time(0)) '[hh:mm:ss]'

Upvotes: 2

Related Questions