# Get Difference between two dates as Hours, Minutes

### Question

• Hi,

I am using the below query to get difference beiween two dates in hours and minutes.Minutes part look ok but the hours is incorrect.

select datediff(minute,LastDate,getdate()) as "Minutes", datediff(hour,LastDate,getdate()) as "Hours"from emp.

Note: Using SQL Server 2005

Regards
Janet

Thursday, September 23, 2010 2:21 PM

• Then Calculate no of minutes then divide it by 60..

```select datediff(minute,@date,getdate())/60
```
• Marked as answer by Thursday, September 23, 2010 4:18 PM
Thursday, September 23, 2010 2:23 PM

### All replies

• Why is that? Can you provide some test data?
Abdallah El-Chal, PMP, ITIL, MCTS
Thursday, September 23, 2010 2:22 PM
• Then Calculate no of minutes then divide it by 60..

```select datediff(minute,@date,getdate())/60
```
• Marked as answer by Thursday, September 23, 2010 4:18 PM
Thursday, September 23, 2010 2:23 PM
• Then Calculate no of minutes then divide it by 60..

```select datediff(minute,@date,getdate())/60
```

Rami,

this solution is like telling the OP there is an issue in the code she posted, yet in fact it might be a data problem.

I suggest we look at the data first and try to find out the root cause of the issue instead of providing an alternative solution.

Abdallah El-Chal, PMP, ITIL, MCTS
Thursday, September 23, 2010 2:27 PM
• Yes Abdshall,

Sorry for that....     Some days back i heard about Whole Number thing, which causes this behaviour... But i think its for days...I am not sure..

Although, I am not able to reproduce the same scenario for hours here, I thought its at some point of time.... so, i didn't ask for re-producing...instead i gave an workaround..........

Thursday, September 23, 2010 2:30 PM

```declare @date datetime = '9/23/2010 07:53:59'
declare @date1 datetime = '9/23/2010 07:59:59'
declare @date2 datetime = '9/23/2010 08:00:00'
select DATEDIFF(hour,@date,@date1),DATEDIFF(hour,@date,@date2)```
• Proposed as answer by Tuesday, October 07, 2014 2:35 PM
• Unproposed as answer by Tuesday, October 07, 2014 2:35 PM
Thursday, September 23, 2010 2:33 PM
• Rami,

I see what you are saying. That's why when the exact time is calculated, for example HH:MM:SS, it not calculated using DATEDIFF function only depending on hour,minutes, and seconds. It is done in a different way, and if thats what the OP wants, here is how she can do that.

```DECLARE @mintime DATETIME = '2009-02-02 11:12:20.123'
DECLARE @maxtime DATETIME = '2009-02-02 13:30:25.123'

declare @duration int
set @duration = datediff([second], @minTime, @maxtime);

SELECT CASE WHEN LEN(H) = 1
THEN '0' + h
ELSE h END
+ ':' +
CASE WHEN LEN(m) = 1
THEN '0' + m
ELSE m END
+ ':' +
CASE WHEN LEN(s) = 1
THEN '0' + s
ELSE s END
FROM
(
select
CAST(@duration / 3600 AS VARCHAR(3)) as [h],
CAST(@duration % 3600 / 60 AS VARCHAR(3)) as [m],
CAST(@duration % 3600 % 60 AS VARCHAR(3))as [s]) AS X
```

Abdallah El-Chal, PMP, ITIL, MCTS
Thursday, September 23, 2010 2:50 PM