Numeric Devision to 10 decimal places

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

SQL Server 2005 Forums

Transact-SQL (2005)

Numeric Devision to 10 decimal places

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2011-01-27 : 15:14:31

Hi All,

I have table called XYZ with two columns Value1 and value2 both numeric type

CREATE TABLE XYZ
(
Value1 numeric,
Value2 numeric,
)

The table has the following values.

INSERT INTO XYZ (Value1,Value2) VALUES (12345678,333)

In my select Query I want to do a division (Value1/Value2) but the result should return value to 10 decimal places. So in this example the result should be 37074.1081081081. How to do that in my SELECT query? Please suggest.

Thanks,

Zee

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-27 : 15:21:55

Either use the ROUND function, or explicitly state the precision you want

SELECT CONVERT(DECIMAL(16,10), (Value1/Value2)) FROM XYZ

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2011-01-27 : 15:33:43

Thanks russell. Can you also tell how to use the rounding function in my example? Thanks.

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2011-01-27 : 17:13:52

Can someone reply using the rounding function please. thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-27 : 17:22:23

SELECT Round(Value1/Value2, 10) FROM XYZ

SQL Server Books Online has details and examples of all the available functions. It's the best resource and official documentation.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-28 : 01:09:19

quote:
Originally posted by zeeshan13

Hi All,

I have table called XYZ with two columns Value1 and value2 both numeric type

CREATE TABLE XYZ
(
Value1 numeric,
Value2 numeric,
)

The table has the following values.

INSERT INTO XYZ (Value1,Value2) VALUES (12345678,333)

In my select Query I want to do a division (Value1/Value2) but the result should return value to 10 decimal places. So in this example the result should be 37074.1081081081. How to do that in my SELECT query? Please suggest.

Thanks,

Zee

When you use numeric or decimal datatype, make sure to specify the size otherwise the value will be rounded and decimal values will be truncated

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources