Formatting Decimal Values from SQL Server Database

Question

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.

score 3 · Accepted Answer · answered Jul 27 '11 at 18:26

3

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

Problem solved.

answered Jul 27 '11 at 18:26

Jonathan Wood

65,341
71
269
466

score 2 · Answer 2 · answered Jul 27 '11 at 18:01

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);

answered Jul 27 '11 at 18:01

Hamid

41
2

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. – Jonathan Wood Jul 27 '11 at 18:07

score 1 · Answer 3 · answered Jul 27 '11 at 17:56

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.

answered Jul 27 '11 at 17:56

Raj More

47,048
33
131
198

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 Jul 27 '11 at 18:04
@Jonathan Wood - The convention for the significant digits is after the decimal point, not before. – Raj More Jul 27 '11 at 18:16
Okay, so wouldn't returning `001.00000` make just as much sense as what it does now? – Jonathan Wood Jul 27 '11 at 18:17

score 1 · Answer 4 · answered Jul 27 '11 at 17:57

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.

answered Jul 27 '11 at 17:57

H H

263,252
30
330
514

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? – Jonathan Wood Jul 27 '11 at 18:03
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. – H H Jul 27 '11 at 19:35
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. – Jonathan Wood Jul 27 '11 at 19:42

score 0 · Answer 5 · answered Mar 10 '21 at 03:08

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

Formatting Decimal Values from SQL Server Database

5 Answers5