[FIXED] How to convert VARCHAR columns to DECIMAL without rounding in SQL Server?

Issue

In my SQL class, I'm working with a table that is all VARCHAR. I'm trying to convert each column to a more correct data type.

For example. I have a column called Item_Cost that has a value like:

1.25000000000000000000

I tried to run this query:

ALTER TABLE <table> 
    ALTER COLUMN Item_Cost DECIMAL

This query does run successfully, but it turns it into 1 instead of 1.25.

How do I prevent the rounding?

Solution

Check out the documentation for the data type decimal. The type is defined by optional parameters p (precision) and s (scale). The latter determines the numbers to the right of the decimal point.

Extract from the documentation (I highlighted the important bit in bold):

s (scale)

The number of decimal digits that are stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point. Scale must be a value from 0 through p, and can only be specified if precision is specified. The default scale is 0 and so 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Defining a suitable precision and scale fixes your issue.

Sample data

create table MyData
(
  Item_Cost nvarchar(100)
);

insert into MyData (Item_Cost) values ('1.25000000000000000000');

Solution

ALTER TABLE MyData Alter Column Item_Cost DECIMAL(10, 3);

Result

Item_Cost
---------
1.250

Fiddle

Answered By - Sander

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)