urgent sql help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

urgent sql help

Author

Topic

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-13 : 06:13:03

hi,
the below query gives the output 0.000
SELECT CAST(99997/100000 AS NUMERIC(18,3))
but i need the output 0.999

please help

Javeed Ahmed

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-13 : 09:32:01

Two problems:

1. the division is of two integers so the decimal places are lost. Add decimal points to the numbers like this:


99997./100000.

2. The result is being rounded to the nearest decimal position.

99997./100000. = .99997

rounding that yields 1.000 which is correct. You either need more decimal places in the result or do it a different way, e.g.


SELECT left(CAST(99997./100000. AS varchar(18)), 5)

SELECT CAST(99997./100000. AS NUMERIC(18,5))

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-13 : 12:00:09

thanks for the input.
if the particular value comes from a column. how do I add the decimal point?

Javeed Ahmed

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-13 : 12:23:32

type it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources