TRY_CONVERT // Converting data to money, smallmoney

marcus

SQL Server provides the following data types for storing currency values:

  • money
  • smallmoney

The data type money is essentially identical to the data type decimal(19, 4) and the data type smallmoney to the data type decimal(10, 4). There are internal differences in how SQL Server stores a decimal value and a money/smallmoney value. However, these differences are not relevant here.

The data types money and smallmoney have a special bevaviour in calculations. The result of a multiplication or division of money values is implicitly converted into a money value with 4 decimal places after each step of the calculation.

 1: SELECT CAST(123.45678 AS money        )              -- 123,4568
 2: SELECT CAST(123.45678 AS money        ) / 100        -- 1,2345
 3: SELECT CAST(123.45678 AS money        ) / 100 * 100  -- 123,45
 4:                                                     -- 
 5: SELECT CAST(123.45678 AS decimal(10,4) )             -- 123.4568
 6: SELECT CAST(123.45678 AS decimal(10,4) ) / 100       -- 1.23456800
 7: SELECT CAST(123.45678 AS decimal(10,4) ) / 100 * 100 -- 123.45680000

When using the data type money, the result loses precision in the first statement. The result in lines 1 and 3 is not nominally the same. Using the data type decimal gives the same result.

The return result of converting a value to the money data type depends on the data type in which a number is passed. A distinction must be made here between passing a parameter expression as a value of type text and as a value of type no text.

Content

Text

If a value of type text is passed, the value passed must represent a number. A point is always expected as the decimal separator. In contrast to converting a number to the decimal data type, commas are ignored. Since decimal numbers supplied via a text file, for example in German notation, use a comma as a decimal separator, a comma should generally be replaced by a period. If the input value contains both a decimal separator and commas as thousands separators, this replacement results in the input value not being able to be interpreted because the input value then has more than one period. In this case, the result of the conversion is NULL.

Leading and trailing spaces are truncated by the function and do not prevent conversion. An empty string is translated as 0 in contrast to the target data type decimal. If the input value contains more than 4 decimal places, the TRY_CONVERT function rounds to the fourth decimal place.

The following code example shows the results of the TRY_CONVERT function for various valid, invalid and sometimes nonsensical decimal numbers that are passed as text of type nvarchar and converted to a number of type money:

 1: SELECT TRY_CONVERT(money, NULL          ) -- > NULL
 2: SELECT TRY_CONVERT(money, N'12345678'   ) -- > 12345678.00
 3: SELECT TRY_CONVERT(money, N'123,45678'  ) -- > 12345678.00
 4: SELECT TRY_CONVERT(money, N'123.45678'  ) -- > 123.4568
 5: SELECT TRY_CONVERT(money, N''           ) -- > 0.00
 6: SELECT TRY_CONVERT(money, N' '          ) -- > 0.00
 7: SELECT TRY_CONVERT(money, N'  123.45678') -- > 123.4568
 8: SELECT TRY_CONVERT(money, N'123.45678  ') -- > 123.4568
 9: SELECT TRY_CONVERT(money, N'12345678E-3') -- > NULL
10: SELECT TRY_CONVERT(money, N'1,234.5678' ) -- > 1234.5678
11: SELECT TRY_CONVERT(money, N'1.234.5678' ) -- > NULL
12: SELECT TRY_CONVERT(money, N'1,2,3,4'    ) -- > 1234.00
13: SELECT TRY_CONVERT(money, N'1,2,3.4'    ) -- > 123.40
14: SELECT TRY_CONVERT(money, N'1,2.3.4'    ) -- > NULL
15: SELECT TRY_CONVERT(money, N'1,2.3,4'    ) -- > 12.34

If a value of type text is passed to the TRY_CONVERT function, the value passed must represent a number. A point is always expected as the decimal separator. Thousand separators are not permitted. If the parameter contains a comma, the value cannot be converted and the TRY_CONVERT function returns NULL. Leading and trailing spaces are truncated by the function TRY_CONVERT and do not prevent conversion. An empty string is translated as NULL.

The following code example shows the results of the TRY_CONVERT function for various valid and invalid decimal numbers passed as text of type nvarchar and converted to a number of type money:

1: SELECT TRY_CONVERT(money, NULL          ) -- > NULL
 2: SELECT TRY_CONVERT(money, N'12345678'   ) -- > 12345678.00
 3: SELECT TRY_CONVERT(money, N'123,45678'  ) -- > 12345678.00
 4: SELECT TRY_CONVERT(money, N'123.45678'  ) -- > 123.4568
 5: SELECT TRY_CONVERT(money, N''           ) -- > 0.00
 6: SELECT TRY_CONVERT(money, N' '          ) -- > 0.00
 7: SELECT TRY_CONVERT(money, N'  123.45678') -- > 123.4568
 8: SELECT TRY_CONVERT(money, N'123.45678  ') -- > 123.4568
 9: SELECT TRY_CONVERT(money, N'12345678E-3') -- > NULL
10: SELECT TRY_CONVERT(money, N'1,234.5678' ) -- > 1234.5678
11: SELECT TRY_CONVERT(money, N'1.234.5678' ) -- > NULL
12: SELECT TRY_CONVERT(money, N'1,2,3,4'    ) -- > 1234.00
13: SELECT TRY_CONVERT(money, N'1,2,3.4'    ) -- > 123.40
14: SELECT TRY_CONVERT(money, N'1,2.3.4'    ) -- > NULL
15: SELECT TRY_CONVERT(money, N'1,2.3,4'    ) -- > 12.34

Lines 10 to 15 require special attention. Here, sometimes nonsensical combinations of decimal separators and thousands separators are used to explain the behavior of the TRY_CONVERT function.

In lines 5 and 6, the input values are an empty string and text with only spaces, respectively. The TRY_CONVERT function converts this to the value 0. This behavior can be viewed as an error in the TRY_CONVERT function and must be taken into account during the conversion in that an empty character string or a text with only spaces must be replaced by a NULL before the conversion .

No Text

If a number is passed as a typed number rather than text, the function can convert any number into a value of type money. Values are rounded to the fourth decimal place. Converting a number value into the target data type money can therefore change the nominal initial value.

The following code example shows the results of the TRY_CONVERT function for various valid and invalid numbers passed as typed values, each converted to a number of type money:

 1: SELECT TRY_CONVERT(money, NULL       ) -- > NULL
 2: SELECT TRY_CONVERT(money, 12345678   ) -- > 12345678.00
 3: SELECT TRY_CONVERT(money, 123,45678  ) -- > 123.00
 4: SELECT TRY_CONVERT(money, 123.45678  ) -- > 123.4568
 5: SELECT TRY_CONVERT(money, 12345678E-5) -- > 123.4568

It should be noted here that the number in line 3 is not a decimal number. The value after the comma is interpreted as a style parameter, which, however, has no effect on the type conversion. In this line, the value in expression is of type int. In line 4, a decimal number with decimal places is passed in expression as a value of type float.

Safe type conversion

Taking into account the above statements, in particular the incorrect behavior of the TRY_CONVERT function when converting an empty character string or a text with only spaces, the conversion of an input value to the money data type is carried out using the following statement:

1: TRY_CONVERT( [money[(n)]|smallmoney]
 2:             ,TRY_CONVERT( float
 3:                          ,REPLACE( CASE WHEN TRIM([Input]) = ''
 4:                                       THEN NULL 
 5:                                       ELSE [Input] 
 6:                                    END
 7:                                   , ','
 8                                    , '.'
 9:                                  )
10:                         )
11:            ) AS [Output]

And here is an example …

1: DECLARE @input as nvarchar(30);
 2: SET @input = '  123.45678';
 3: SELECT TRY_CONVERT(
 4:            money
 5:           ,TRY_CONVERT( float
 6:                        ,REPLACE( CASE WHEN TRIM(@input) = ''
 7:                                     THEN NULL 
 8                                      ELSE @input
 9:                                  END
10:                                 , ','
11:                                 , '.'
12:                                )
13:                       )
14:            ) AS [Output]

Summary

When converting a value to the data type money, several special cases must be taken into account: Empty character strings are converted to the number 0, numbers in scientific notation cannot be converted, thousands of separators are not permitted and only the decimal point is permitted as a decimal separator. The statement shown here takes these special cases into account when converting a value to the data type money.

Although the rounding problem described above plays a minor role when converting a value in an ETL process, it must be taken into account in calculations that are carried out in an ETL process with money values and, if necessary, the data type decimal should be preferred over the data type money.

Related Posts