# Datetime Difference

• ### Question

•  Hi all, I have a rather basic question, but having difficulty.... I need to calculate the difference between 2 times. Any help would be appreciated. Example: 09:28:00 - 09:38:00 = 00:10:00

Dave SQL Developer

Friday, September 6, 2013 6:35 PM

• `SELECT CAST(DATEDIFF(s, Start, End) / 3600 AS VARCHAR(2)) + ':' + CAST((DATEDIFF(s, Start, End) / 60) % 60 AS VARCHAR(2)) + ':' + CAST(DATEDIFF(s, Start, End) % 60 AS VARCHAR(2))`

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

• Marked as answer by Friday, September 6, 2013 7:32 PM
Friday, September 6, 2013 7:29 PM

### All replies

http://technet.microsoft.com/en-us/library/ms189794.aspx

vt

• Edited by Friday, September 6, 2013 6:41 PM
Friday, September 6, 2013 6:36 PM

Dave SQL Developer

Friday, September 6, 2013 6:41 PM
• pls see the 2nd link provided

vt

Friday, September 6, 2013 6:43 PM
• Anyone can just run a Google search.

Dave SQL Developer

Friday, September 6, 2013 6:43 PM
• ```declare @x as time = '09:28:00'
declare @y as time = '09:38:00'
declare @z as time
set @z = DATEADD(s, datediff(s, @x, @y), '00:00:00')
print @z```

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Friday, September 6, 2013 6:44 PM
• * Sigh...

Ok, this is not hardcoded. Here is what I am trying to do but it's not working out...

datediff(hh, startdate, enddate)+':'+datediff(mm, startdate, enddate)+':'datediff(ss, startdate, enddate)

Dave SQL Developer

Friday, September 6, 2013 7:07 PM
• Hi,

The hours part is DATEDIFF(s, Start, End) \ 3600

The minutes part is (DATEDIFF(s, Start, End) \ 60) % 60

The seconds part is DATEDIFF(s, Start, End) % 60

Convert them all to strings and concatenate them

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Friday, September 6, 2013 7:16 PM
• Incorrect syntax near '\ 3600'.

Could you please give real syntax?

Dave SQL Developer

Friday, September 6, 2013 7:19 PM
• Oops... typing mistake

Here it is

The hours part is DATEDIFF(s, Start, End) / 3600

The minutes part is (DATEDIFF(s, Start, End) / 60) % 60

The seconds part is DATEDIFF(s, Start, End) % 60

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Friday, September 6, 2013 7:20 PM
• OK, could you please write it out so it works. I have been working on this for several hours and would appreciate it. Copy and pasting from Google is not why we use this forum.

Thank you kindly

Dave SQL Developer

Friday, September 6, 2013 7:26 PM
• Sorry man this doesn't work at all.

Dave SQL Developer

Friday, September 6, 2013 7:28 PM
• My result is zero. Again, please do not copy and paste from google to get the question answered.

Dave SQL Developer

Friday, September 6, 2013 7:29 PM
• `SELECT CAST(DATEDIFF(s, Start, End) / 3600 AS VARCHAR(2)) + ':' + CAST((DATEDIFF(s, Start, End) / 60) % 60 AS VARCHAR(2)) + ':' + CAST(DATEDIFF(s, Start, End) % 60 AS VARCHAR(2))`

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

• Marked as answer by Friday, September 6, 2013 7:32 PM
Friday, September 6, 2013 7:29 PM
• ```  Declare @StartDate as time = '09:28:00'
Declare @EndDate as time =  '09:38:00'

SELECT @StartDate, @EndDate ,
Coalesce( Convert(varchar(5),DateDiff(day, (Cast(@EndDate as datetime)- Cast(@StartDate as datetime)),'1900-01-01'))  + ':' + Convert(varchar(10),(Cast(@EndDate as datetime)-Cast(@StartDate as datetime)), 108),'00:00:00:00') as [days:hh:mm:ss]
```

Friday, September 6, 2013 8:39 PM