We help IT Professionals succeed at work.

sql server - decimal vs real datatypes

769 Views
Last Modified: 2012-05-10
How many decimal places for the decimal vs real datatypes? what would happen if i updated a real datatype to decimal.

some of the values are like this for example:

0.879999995231628
Comment
Watch Question

EA
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
if i change the real to decimal(38, 0) I would have 38 total characeters correct?
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Lara FEA
CERTIFIED EXPERT

Commented:
decimal(38,0)  means no decimal point. You real number will be converted to 1
declare @dec decimal(38,0),      @real real

set @real=0.879999995231628
set @dec=@real
select @dec

Author

Commented:
It would round to the nearest what?

Author

Commented:
What decimal should i used then?

Author

Commented:
I want to have some decimal places
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
A real datatype like any other float is very imprecise.  If your data requires precise values, changing to the decimal is the right way to go.
http://msdn.microsoft.com/en-us/library/ms173773.aspx

Now it seems your question is the right Decimal value to use.  The question there would come in what is the number of decimal places you want to go out to and do you expect to get a equally long whole number.

0.879999995231628

If the above represents your target data and have no whole number values, you could use:

decimal(15, 15)

Alternatively you could go decimal(30, 15) or decimal(38, 19) and split down the middle to accept :
879999995231628.0
or
0.879999995231628
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Arg. I guess I should have said "ditto" since Scott said the same thing here http:#a33676855

To exemplify Scott's point:
declare @real real
set @real = 0.879999995231628;
select convert(decimal(38, 10), @real);
-- results in 0.8799999952 << rounded to 10th decimal place

Author

Commented:
so that i undertand

decimal(15, 15)


would allow 15 to the left and 15 to the right of the decimal point?

decimal(13, 17)

would allow 13  to the left and 17 to the right of the decimal point?

Author

Commented:
OK i undestand now. thank you
Lara FEA
CERTIFIED EXPERT

Commented:
decimal(N,M)
M will specify number of digit after decimal point, N total number of digits

decimal(13, 17) is invalid
The scale must be less than or equal to the precision.

decimal(15, 15)
will have only 15 position AFTER decimal point


Author

Commented:
OK thank you
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Yes, agree with previous Expert.  To allow 15 to left and right, you will need decimal(30, 15) as I said in my post.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
>> It would round to the nearest what? <<

As I stated, "SQL will round as necessary to meet the new definition".

So however many decimal places are in the new column data type, SQL will automatically round to that.


Author

Commented:
ty

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.