>>>>>What are the types of these columns?
>>>>
>>>>Thanks I solved it. The types were float and money. Casting to numeric fixes it.
>>>
>>>Exactly. That was my first thought - that both or one of them is float.
>>
>>This rounding has been working for a couple of months now, is it that float only causes problems in certain situations?
>
>Every data type in SQL Server has certain problems. But float and money are the types that lead to the most number of rounding errors.
>
>Search MSDN forum for Money vs. Decimal discussions, say,
>http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/f24acf6c-a518-4f0a-a13e-0172e700564c

Thanks Naomi,

I never use these data types in my solutions so was unaware of the problems. In this case I'm modifying a system written by someone else who used these types everywhere.