Avatar of mpdillon
mpdillon asked on

How to return a decimal from DateDiff

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
Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
Patrick Matthews

8/22/2022 - Mon
Patrick Matthews

SELECT DATEDIFF(mi, DateTime1, DateTime2) / 60.0 AS DecimalHours, DATEDIFF(mi, DateTime1, DateTime2) AS Minutes
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Patrick Matthews

In my testing with SQL Server 2000, the CAST was unnecessary--by dividing by 60.0 rather than
60, SQL Server did an implicit conversion.

Now, if you want to exactly specify the data type down to overall precision and scale, then yes, you'd \
need CAST of CONVERT...
Mark Wills


You can always try :

select datediff(hh,'05/08/2008 12:00','05/08/2008 16:44') + ((datediff(mi,'05/08/2008 12:00','05/08/2008 16:44') % 60) / 60.0)

or if really planning for time diff to be 285 minutes, then try:

select datediff(hh,'05/08/2008 12:00','05/08/2008 16:45') + ((datediff(mi,'05/08/2008 12:00','05/08/2008 16:45') % 60) / 60.0)

or, if wanting the difference to be inclusive of times, then try:

select datediff(hh,'05/08/2008 12:00','05/08/2008 16:44') + (((datediff(mi,'05/08/2008 12:00','05/08/2008 16:44')+1) % 60) / 60.0)

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
mpdillon

This answer worked just fine. I didn't test the answers that came after this one. Thanks for your help
Guy Hengel [angelIII / a3]

<Grading Comments>
This answer worked just fine. I didn't test the answers that came after this one. Thanks for your help
</Grading Comments>

thanks, but did you test the one before mine?
Patrick Matthews

mpdillon,

Did you try to implement my suggestion?  My own testing in SQL Server 2000 indicated that using
CAST or CONVERT was not necessary at all, unless you had to have complete control over both the
overall precision and scale of the decimal result.  And, seeing as you made no mention of that in your
question, it must not have been important to you to have that full measure of control...

Regards,

Patrick
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.