# Difference between time periods

• ### Question

• Hi, I am trying to get the difference between StartTime and EndTime of a session.

SELECT DATEDIFF(mi, '08:55', '11:00')/60;

The above query returns 2 hours, but I am looking to output 2 hours & 5 mins. How do I do my calculation to get the difference in Hours:Minutes. I am using SQL Server 2008 R2.

Tuesday, December 11, 2012 6:37 PM

• In this case

`SELECT CAST(DATEDIFF(mi, '08:55', '11:00')/60.0 as decimal(10,3)) `

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Tuesday, December 11, 2012 6:59 PM

### All replies

• Try

```SELECT CAST(DATEDIFF(mi, '08:55', '11:00')/60 as varchar(10)) + ':' +
RIGHT('00'+CAST(DATEDIFF(mi, '08:55', '11:00')%60 as varchar(10)),2)```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Tuesday, December 11, 2012 6:40 PM
• Hi Naomi, Thanks for your response. I actually want 2 hours & 5 mins as 2.083. Apologize for not mentioning it on the original post.
Tuesday, December 11, 2012 6:53 PM
• `SELECT CAST(DATEDIFF(mi, '08:55', '11:00')/60. as decimal(6,3))`

Tuesday, December 11, 2012 6:58 PM
• In this case

`SELECT CAST(DATEDIFF(mi, '08:55', '11:00')/60.0 as decimal(10,3)) `

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Tuesday, December 11, 2012 6:59 PM
• try this...

```-- Build some sample data
DECLARE @Ranges TABLE (
Start DateTime,
Finish DateTime)
INSERT INTO @Ranges VALUES
('20110101 05:05:05.000','20110108 06:06:06.000'),
('20110101 05:05:05.000','20110108 07:07:07.000'),
('20110101 05:05:05.000','20110110 09:09:09.000'),
('20110101 05:05:05.000','20110114 10:11:12.000'),
('20110101 05:05:05.000','20110116 23:54:45.000'),
('20110101 05:05:05.000','20110122 17:18:16.000')
-- The actual solution
SELECT
CAST(DATEDIFF(ss, Start, Finish) / 86400 AS INT) AS [Days],
CAST(DATEDIFF(ss, Start, Finish) % 86400 / 3600 AS INT) AS [Hours],
CAST(DATEDIFF(ss, Start, Finish) % 86400 % 3600 / 60 AS INT) AS [Minutes],
DATEDIFF(ss, Start, Finish) % 86400 % 3600 % 60 AS [Seconds],
CAST(CAST(DATEDIFF(ss, Start, Finish) / 86400 AS INT) AS VARCHAR(5)) + ' Days, ' +
CAST(CAST(DATEDIFF(ss, Start, Finish) % 86400 / 3600 AS INT) AS VARCHAR(5)) + ' Hours, ' +
CAST(CAST(DATEDIFF(ss, Start, Finish) % 86400 % 3600 / 60 AS INT) AS VARCHAR(5)) + ' Mins, ' +
CAST(DATEDIFF(ss, Start, Finish) % 86400 % 3600 % 60 AS VARCHAR(5)) + 'Sconds' AS AmtOfTime
FROM @Ranges```

HTH,

Jason

Jason Long

Tuesday, December 11, 2012 7:00 PM
• Thanks all that worked. I ended up using Naomi's solution.
Tuesday, December 11, 2012 7:07 PM