T-Sql Remove Decimal Point From Money Data Type

Question

Given the constraint of only using T-Sql in Sql Server 2005, is there a better way to remove the decimal point from a money datatype than a conversion to a varchar (here implicitly) and then a replace of the decimal point?

Here is what I have currently.

SELECT REPLACE(1.23, '.', ''), REPLACE(19.99, '.', '')

Which returns the desired 123 and 1999, but I was wondering if there was a better way. Any thoughts?

Answer 1

Multiply by 100 and then convert to an int.

Answer 2

You should never ever use the money datatype to store monetary values. If you do any calculations you will get truncated results. Run the following to see what I mean

DECLARE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4)

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS moneyresult,
@num4 AS numericresult

Output: 2949.0000 2949.8525