troubleshooting Question

How to return a decimal from DateDiff

Avatar of mpdillon
mpdillon asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
7 Comments1 Solution6754 ViewsLast Modified:
I would like to determine the difference between two dates. I would like that difference returned in Hours and partial hours. So a datediff of 90 minutes would be shown as 1.5 hours.
In the query below I calculate DateDiff in minutes and then divide by 60. Even though I cast the result as a decimal, my result is still rounded to an integer.
The results are Hrs = 4 and Min = 285
Should be hrs = 4.75 and Min = 285

How do I return partial hours?

SELECT     CAST(DATEDIFF(mi, '05/08/2008 12:00', DATEADD(mi, 1, '05/08/2008 16:44')) / 60 AS decimal(5, 2)) AS Hrs, CAST(DATEDIFF(mi, '05/08/2008 12:00',  DATEADD(mi, 1, '05/08/2008 16:44')) / 60 AS decimal(5, 2)) AS Min
FROM       TableName
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros