1

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
Community
  • 1
  • 1
Dinesh Reddy Alla
  • 1,677
  • 9
  • 23
  • 47
  • What if difference > 24 hours. Can you give example output? – Giorgi Nakeuri Nov 03 '15 at 11:20
  • this is the of similar question http://stackoverflow.com/questions/21249778/datediff-to-output-hours-and-minutes – Milen Nov 03 '15 at 11:21
  • Possible duplicate of [SQL time difference between two dates result in hh:mm:ss](http://stackoverflow.com/questions/13577898/sql-time-difference-between-two-dates-result-in-hhmmss) – J-D Nov 03 '15 at 11:26
  • Mr. J.D first read question carefully...... and then mark it as duplicate. @J-D – Dinesh Reddy Alla Nov 03 '15 at 11:49

2 Answers2

4

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
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
2

You can use CAST

Select CAST((@jobEndDate-@jobStartDate) as time(0)) '[hh:mm:ss]'
MusicLovingIndianGirl
  • 5,909
  • 9
  • 34
  • 65