Formatting with decimal 1

[gatetec]

SELECT count(TRANSTYPE)/(select datediff (day, '11/10/2005', '12/31/2008')) as CntperDay
FROM dbo.PTINFO

Please advise how to format this in decimal i.e. 51.23

Thanks so much

 

[RiverGuy]

You're dividing an integer by an integer, so you need to convert one of them to a decimal to get a decimal.

SELECT  CONVERT(DECIMAL(18,2), count(TRANSTYPE))/(select datediff (day, '11/10/2005', '12/31/2008')) as CntperDay
FROM dbo.PTINFO

 

[gatetec]

CONVERT(DECIMAL(18,2) ... gives 51.1691368788142
whereas the result should have been 51.16 or 51.17 with rounded up, which is preferred. How do you modify this?
Please advise.

Thank you

 

[RiverGuy]

First you have to convert to a DECIMAL data type like I've shown above. Then you can use the ROUND function from there.

SELECT  ROUND(CONVERT(DECIMAL(18,2), count(TRANSTYPE))/(select datediff (day, '11/10/2005', '12/31/2008')),2) as CntperDay
FROM dbo.PTINFO

 

[gatetec]

Thank you for the tip. I got the right number.
One thing I am curious why there are many extra zeros after .17 the result I have is 51.1700000000000

Please advise.

 

[gmmastros]

It's complicated. Mostly... it's got to do with data types. You're taking a Decimal(18,2), dividing an integer, and then rounding.... Where the data type ends up is anyones guess.... or is it???

Take a look at this blog I wrote to get a better understanding.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-precision-and-scale-problems

If you want 2 digits of precision, at the end, then the last thing you should do is convert it to decimal(18,2).

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gatetec]

Thanks so much for the tip.