Geek Answers Handbook

T-Sql Remove decimal point from money data type

Given the limitation of using T-Sql in Sql Server 2005, is there a better way to remove the decimal point from the money data type than converting to varchar (implicit here) and then replacing the decimal point?

Here is what I have at the moment.

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

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

+7
sql-server tsql sql-server-2005
source share
5 answers

Multiply by 100 and then convert to int.

+6
source share

You should never use a money data type to store monetary values. If you do any calculations, you will get truncated results. Follow the steps below 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

+3
source share

Could you be more specific if used? Removing a decimal point from a view is a bit unusual, given that you lose all the scale information. Do you assume that there will always be two digits? If so, you can simplify the multiplication by 100, and then round to conversion to string.

0
source share

Keep in mind that the data type of money can have up to 4 digits after the decimal. Values ​​with more than two digits may not work as expected for your original solution or x100 trick.

0
source share

Here is the magic:

 DataFormatString="{0:c0}

This will remove the decimal places.

0
source share

More articles:


All Articles