Solved

T-SQL and Rounding

Posted on 2010-09-14
1
307 Views
Last Modified: 2012-06-21
I have the following t-sql...

SUM(DATEDIFF(MINUTE, CONVERT(datetime, tblProduction.startDate, 101) + ' ' + CONVERT(datetime, tblstarttime.time, 108), CONVERT(datetime, tblProduction.endDate, 101) + ' ' +
CONVERT(datetime, tblendtime.time, 108)) / 60) AS totalhours

It calculates the correct # of minutes, but when I /60 to get the hours, it rounds down.

How should I update this so when 30 minutes is calculated, I get 0.5 hours etc.

0
Comment
Question by:Jedidia
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 33672087
try this:


SUM(cast( DATEDIFF(MINUTE, CONVERT(datetime, tblProduction.startDate, 101) + ' ' + CONVERT(datetime, tblstarttime.time, 108), CONVERT(datetime, tblProduction.endDate, 101) + ' ' +

CONVERT(datetime, tblendtime.time, 108)) as decimal(20,4)) / 60.0) AS totalhours

Open in new window

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now