CODEWITHSUNDEEP
sqlsql-serverintegerdecimaltype-conversion
user3115933
user3115933

Reputation: 4443

How to change data type of a column in an SQL table from integer to decimal

I have assigned the data type of one of the columns of a table I have created as int. My problem is that it is not showing decimal places when I run a query against it.

How do I correct the data type of this column so that it accepts decimal places?

Table is dbo.Budget and the column concerned is called ROE.

Upvotes: 24

Views: 113674

Answers (4)

saktiprasad swain
saktiprasad swain

Reputation: 340

Alter datatype of that column ..But In general sql wont allow to channge.It will prompt u drop that column..There is setting to achive that thing.
Go to Tool-Option-designers-Table and Database designers and Uncheck Prevent saving option.I m taking abt sql server 2008R2enter image description here

Upvotes: 1

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

Just to have this stated clearly:

If there is no data in the table, or not that much, then the simple ALTER TABLE statement (as described in the other answers here) is fine.

But, if there is a lot of data (millions of rows, or possibly less depending on the size of the table) and/or a lot of contention on the table and not much opportunity for a full downtime / maintenance window, then it requires a different approach, such as what I described in this answer: Narrowing the Data Types on a very large table.

Upvotes: 3

marc_s
marc_s

Reputation: 754478

Easy - just run this SQL statement

ALTER TABLE dbo.Budget
ALTER COLUMN ROE DECIMAL(20,2)   -- or whatever precision and scale you need.....

See the freely available MSDN documentation on what exactly the precision, scale and length in decimal numbers are and what ranges of values are possible

Upvotes: 41

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

You can execute this simple sql statement

Alter table yourtable
Alter Column yourtable_column Decimal(10,2)

you can set decimal precision and scale whatever you need.

Upvotes: 3

Related Questions