tsql casting to money rounds up

Question

When casting a varchar value to MONEY it is rounding the value to the nearest 0.10, how do I prevent this rounding up?

UPDATE: I found the problem. In a subquery, the value is being CAST from varchar to FLOAT and then I was trying to CAST from FLOAT to MONEY.

Answer 1

I am not sure i understand your problem.

When looking at the code below

DECLARE @money AS MONEY,
     @varchar AS VARCHAR(20)

SET @varchar = '1000.456789'

SELECT CAST(@varchar AS MONEY)
SELECT @money = @varchar
SELECT @money

it gets rounded to the nearest 4th decimal, not the 1st decimal.

Answer 2

See this very interesting blog post by Brad Schulz on that exact topic:

Throw Your MONEY Away

He advocates never even using the MONEY datatype for various reasons - interesting and thought inspiring read!

Marc