2

I'm working with an existing SQL Server database, with a table column defined as decimal(4,2). However, I've been asked to prevent the code from rounding decimal places.

So I tried changing the column type to decimal, which is automatically converted to decimal(18, 0), which promptly wiped out any fractional portion of my existing values. (Nice. The default decimal type is an integer. What's the point?)

So I tried changing the column type to decimal(8,5). Since 5 digits after the decimal is more than needed, this eliminates the rounding issue.

However, whenever I print this value in a C# ASP.NET application, it is always formatted with 5 digits after the decimal (e.g. 12.34000). I don't understand this. When I read the value from the database, I assign it to a regular decimal value. So how does the decimal value "know" there should be 5 digits after the decimal point?

More importantly, how can I display this value without the trailing zeros? I'm guessing there is a way to format the value. However, since it's used in many places it would be much better if it simply didn't automatically append trailing zeros. If needed, I can change the data type in the database.

Improve this question

5 Answers 5

Reset to default
3

I changed the database column to float, and set the C# variable type to double.

Problem solved.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

You should really think about formatting your currency in the presentation layer, not the data layer. Generally, you want to store currency values in a decimal(10,18) field. you can retrieve the decimal value and format it for presentation in csharp like str = String.Format("{0:C}", myDecimalValue);

Improve this answer

1 Comment

Absolutely. That's why it's so frustrating that my presentation layer is wanting to format it according to the internal representation. So this means I need to go add formatting code in dozens of places. Perhaps I'm using the wrong data type. And C is the format for currency. This isn't a currency.
1

Decimal will always come back with trailing zeroes. This is by convention - trailing zeroes are there to show the number of significant digits.

In your case, decimal (8, 5) means that the number has a total of 8 digits, 5 of which are after the decimal point.

Try this Select CAST (1 as decimal (8, 5)) will return back 1.00000

Since this means only 3 digits before the decimal point the following statement Select CAST (1000 as decimal (8, 5)) will give you an error Arithmetic overflow error converting int to data type numeric.

Improve this answer

3 Comments

So why doesn't your first example return 00000001.00000. Wouldn't that make the same amount of sense? Am I just using the wrong data type here?
@Jonathan Wood - The convention for the significant digits is after the decimal point, not before.
Okay, so wouldn't returning 001.00000 make just as much sense as what it does now?
1

Try this little snippet in a Console app:

decimal d1 = 12.34M, d2 = 12.3400M;

Console.WriteLine("{0} {1}", d1, d2);
Console.WriteLine("{0:0.0###} {1:0.0###}", d1, d2);

If, How and Where you can apply a formatting is up to you.

Improve this answer

3 Comments

If I hover over the decimal value in the debugger, it shows "12.34". If that's good enough for Visual Studio, why isn't there a more straight forward way to format this way? It's problematic to find everywhere in the program the column is displayed and implement a formatting string. Am I using the wrong data type?
About the data-type: Float or Real would solve your trailing zeroes, but see how they do with respect to rounding. They are approx types.
The data actually represents a tax rate. So it might be something like 6.625 or 14.25. Seems like decimal is overkill in the first place, even aside from the formatting issues.
0

Entity Framework uses decimal(18,2) by default. If you do not tell it otherwise, it will not format to fit the SQL Server data type.

To tell EF what the SQL data type should be do this for each property:

public class DbEntities : DbContext {
    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        modelBuilder.Entity<Location>().Property(location => location.Latitude).HasPrecision(9, 6);
    }
}

There are also other ways to do this for all properties: Decimal precision and scale in EF Code First

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.