I am at a loss trying to convert the data that was sent to me.

Question

They used real  for everything.

We have the data as decimal(5,2) or Int

some of the conversions were not bad since they stored 106 in the real and we have it as an int.

so I just did

ALTER TABLE labdata
ALTER COLUMN [LDL] int null

and this worked fine

then I find some very weird things.

they have real column with values like 10.5 I can set it to decimal 5,1 but NOT 5,2.

then the next column might have 10.54 and again I can set it to decimal 5,1 but not 5,2

WHY?? I am lost how can I set these to decimal 5,2 and be done with it..

ps I found one column that has 16.683...they just said make it 16.68 we want decimal (5,2) for ALL values just like we had it.

what can I do to get this done?

Answer 1

It's really hard to guess without a concise and complete example. Especially as this works:

DECLARE @r REAL = 10.54;

SELECT CAST(@r AS DECIMAL(5,2));