CODEWITHSUNDEEP
sqlsql-servercasting
Sonic Soul
Sonic Soul

Reputation: 24919

Is there a way to cast float as a decimal without rounding and preserving its precision?

So it appears that if you

CAST(field1 as decimal) field1

this will automatically add rounding.

The original is defined as:

field1 type:float length:8 prec:53

I need to cast it to decimal, because I need my Entity Framework layer to generate this field as decimal (instead of double).

Is there a way to cast it as decimal, so that it preserves original precision, and doesn't round?

I would like to avoid having to declare the precision in the cast, because:

  1. there are 100's of fields involved with varying precision, and;
  2. if the underlying table changes in the future, it could cause unforeseen bugs to emerge, and;
  3. makes the management more difficult.

Upvotes: 30

Views: 252133

Answers (3)

Sadhir
Sadhir

Reputation: 423

Try SELECT CAST(field1 AS DECIMAL(10,2)) field1 and replace 10,2 with whatever precision you need.

Upvotes: 3

Abe Miessler
Abe Miessler

Reputation: 85056

Have you tried:

SELECT Cast( 2.555 as decimal(53,8))

This would return 2.55500000. Is that what you want?

UPDATE:

Apparently you can also use SQL_VARIANT_PROPERTY to find the precision and scale of a value. Example:

SELECT SQL_VARIANT_PROPERTY(Cast( 2.555 as decimal(8,7)),'Precision'),
SQL_VARIANT_PROPERTY(Cast( 2.555 as decimal(8,7)),'Scale')

returns 8|7

You may be able to use this in your conversion process...

Upvotes: 55

JeffO
JeffO

Reputation: 8043

cast (field1 as decimal(53,8)
) field 1

The default is: decimal(18,0)

Upvotes: 2

Related Questions