Change SQL column from Float to decimal type

Question

CREATE TABLE [dbo].[TES_Tracks](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Rate] [float] NULL,
[TOL] [datetime] NOT NULL
)

I want to change the speed column to decimal (28.6). I already have a lot of data in this table in float format. I'm afraid of data loss. how should i go for it?

Answer 1

You can simply update the Rate data and then change the data type of the column.

First you can check CAST using the following query (only for decimal places <0.000001)

SELECT 
  [Rate],
  CAST([Rate] as decimal(28, 6)) Rate_decimal
FROM [dbo].[TES_Tracks]
WHERE [Rate] - FLOOR([Rate]) < 0.000001;

Once you confirm the validity of the CAST expression, you can apply it using the UPDATE statement. Again, you can only update rows that have one [Rate] - FLOOR([Rate]), thereby getting good performance.

UPDATE [dbo].[TES_Tracks]
SET [Rate] = CAST([Rate] as decimal(28, 6))
WHERE [Rate] - FLOOR([Rate]) < 0.000001;

ALTER TABLE [dbo].[TES_Tracks] ALTER COLUMN [Rate] DECIMAL(28,6);

, Rate.

- SQL Fiddle

Answer 2

, ...

ALTER TABLE [dbo].[TES_Tracks] ADD [RateNew] DECIMAL(28,6);

UPDATE [dbo].[TES_Tracks] set RateNew = Cast(Rate as Decimal(28,6));

sql- float, , .

Select * From [dbo].[TES_Tracks] where Rate <> RateNew;

, ...

ALTER TABLE [dbo].[TES_Tracks] DROP COLUMN [Rate];

RateNew Rate

EXEC sp_RENAME 'TES_Tracks.RateNew' , 'Rate', 'COLUMN'