• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1992
  • Last Modified:

SQL Query not showing decimals

I have the SQL Query:
CAST((6000/1400) AS DECIMAL(10,7))
I want the result to be 4.2857143, but instead the result is 4.0000000.  It's displaying the whole number but no decimal numbers.  I'm still a SQL novice, so I'm guessing I missed something.
0
David XF
Asked:
David XF
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>CAST((6000/1400) AS DECIMAL(10,7))

One of the 'features' of SQL Server is that if two integers participate in a divide operation, it assumes that the result should be an integer.  

To prevent this from happening, either the numerator or demonator, and not the result, needs to be cast as a numeric value that supports decimal places.
SELECT 6000/CAST(1400 AS DECIMAL(19,4)) AS RESULT
SELECT CAST(6000 AS DECIMAL(19,4))/1400 AS result

Open in new window

0
 
Mike_YashimskiCommented:
Depending on the needs of the query you can also simply add .0 at the end of one of your integer values. This accomplishes the same as above. I would recommend only using this for quick ad hoc queries. Use the above method for any queries that will be used more than once.

CAST((6000.0/1400) AS DECIMAL(10,7))
0
 
David XFSystems AdministratorAuthor Commented:
Thanks.  Exactly what I needed to know.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now