CODEWITHSUNDEEP
sqlsql-servercastingroundingtruncate
AndrewWhitmore
AndrewWhitmore

Reputation: 23

SQL ROUND() function with truncate takes 119.1 and returns 119.09

I have data for pounds and pence stored within concatenated strings (unfortunately no way around this) but can not guarantee 2 decimal places.

E.g. I may get a value of 119.109, so this must translated to 2 decimal places with truncation, i.e. 119.10, NOT 119.11.

For this reason I am avoiding "CAST as Decimal" because I do not want to round. Instead I am using ROUND(amount, 2, 1) to force truncation at 2 decimal places.

This works for the most part but sometimes exhibits strange behaviour. For example, 119.10 outputs as 119.09. This can be replicated as:

ROUND(CAST('119.10' AS varchar),2,1)

My target field is Decimal(19,4) (but the 3rd and 4th decimal places will always be 0, it is a finance system so always pounds and pence...).

I assume the problem is something to do with ROUNDing a varchar....but I don't know anyway around this without having to CAST and therefore introduce rounding that way?

What is happening here?

Any ideas greatly appreciated.

Upvotes: 2

Views: 536

Answers (1)

GarethD
GarethD

Reputation: 69789

This is due to the way floating point numbers work, and the fact that your string number is implicitly converted to a floating point number before being rounded. In your test case:

ROUND(CAST('119.10' AS varchar),2,1)

You are implicitly converting 119.10 to float so that it can be passed to the ROUND function, 119.10 exactly cannot be stored as a float, proven by running the following:

SELECT CAST(CONVERT(FLOAT, '119.10') AS DECIMAL(30, 20))

Which returns:

119.09999999999999000000

Therefore, when you round this with truncate you get 119.09.

For what it is worth, you should always specify a length when converting to, or declaring a varchar

Upvotes: 3

Related Questions