Alter numeric column to allow NULL

Question

hi all

  while i am trying to Alter numeric column to allow NULL,i lost my digits after decimal point.How to alter the numeric column without lose the data after decimal.

Thanks.

---

Thanks - SelvaKumarSubramaniam.Please MARK AS ANSWER, if my answer is useful to U.

Answer 1

Try below one

alter table tablename alter column columnname float null

or

Find out the column datatype and precision and scale value. After that

alter table tablename alter column columnname decimal(prec,scale) null

Answer 2

Hello Selvons,

Not able to reproduce the scenario.Please give some samples.

Drop table t1
CREATE TABLE t1 (Id INT, Count1 numeric(10,2) not null)
INSERT INTO t1 (Id,count1) VALUES (1,0.02);
Select * from t1
Alter table t1 Alter column count1 numeric(10,2) null
Select * from t1

Answer 3

Looks you didn't specify the precision and scale during column property change.

The default scale when no scale value is 0. So, it returned only the Integer part.

select  cast (1.34  as numeric )
select  cast (1.34  as numeric(7,4))

Have a look at http://msdn.microsoft.com/en-us/library/ms187746.aspx

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

While altering the Numeric column, did you change DECIMAL precision and scale too??

I think you might be altering the scale because, run the below example in ssms.

CREATE TABLE #TABLE
(
Sal NUMERIC(10,5) NOT NULL
)

INSERT INTO #TABLE VALUES(5.123), (423.13223), (444.44)

SELECT * FROM #TABLE

ALTER TABLE #TABLE
ALTER COLUMN Sal NUMERIC(10,5)

SELECT * FROM #TABLE

DROP TABLE #TABLE

There is no change in the data.

Please mark as answered if this has helped you solve the issue.

Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.