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

Question

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.

score 38 · Accepted Answer · edited Nov 27 '14 at 16:31

38

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

edited Nov 27 '14 at 16:31

Pரதீப்

91,748
19
131
172

answered Nov 27 '14 at 16:29

marc_s

732,580
175
1,330
1,459

12

Be careful: if you had `NOT NULL` on that column, you need to specify it also in alter statement, otherwise it would accept nulls now – przno Oct 15 '15 at 10:04

score 3 · Answer 2 · answered Nov 27 '14 at 16:30

3

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.

answered Nov 27 '14 at 16:30

HaveNoDisplayName

8,291
106
37
47

score 3 · Answer 3 · edited May 23 '17 at 11:59

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.

score 1 · Answer 4 · answered May 31 '17 at 06:08

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 2008R2

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

4 Answers4