Data quality in SQL Server // TRY_CONVERT for money and smallmoney done safely

Published Mar 3, 2024 · Updated Sep 1, 2026
by

Anyone who has imported a point-of-sale report with values like '1.234,56 €' from a CSV into a SQL Server database knows the pattern: TRY_CONVERT(money, '1,234.56') yields 1234.5600. Yet TRY_CONVERT(money, '1.234,56') yields NULL. And even when the import runs cleanly: money / 100 * 100 is not necessarily the same as the input value.

At a glance:

  • money (±922,337,203,685,477, 8 bytes) and smallmoney (±214,748.3647, 4 bytes) store a fixed four decimal places — their value range can be mapped completely to decimal(19, 4) and decimal(10, 4) respectively.
  • The rounding pitfall: money / 100 * 100 is not necessarily the input value, because every intermediate result implicitly rounds to four decimal places. For multi-step arithmetic, decimal(p, s) is the better choice.
  • With text inputs, TRY_CONVERT ignores the comma ('123,45' becomes 12345.00), and the empty string converts to 0.00. The safe pattern handles both cases.
  • In Postgres, numeric(19, 4) is the portable mapping — the Postgres-specific money type depends on the session locale lc_monetary. A direct try counterpart is missing, this article shows the PL/pgSQL wrapper fn_try_cast_numeric_19_4.

Prerequisite: TRY_CONVERT has existed since SQL Server 2012. The safe pattern additionally uses TRIM and therefore needs SQL Server 2017+ (before that, LTRIM(RTRIM(…))). All examples work with inline literals and no sample database. The Postgres side requires no particular version, only the pg_input_is_valid note needs Postgres 16 or newer.

Content

Range and storage

SQL Server offers two data types for storing currency values. Both have a fixed four decimal places. Their value range can be mapped completely to a decimal(p, 4) with a matching total digit count. Still, money and decimal are not the same thing: storage and behavior in calculations differ (see the rounding pitfall).

TypeMinMaxBytesDecimal placesdecimal mapping
money−922,337,203,685,477.5808+922,337,203,685,477.580784decimal(19, 4)
smallmoney−214,748.3648+214,748.364744decimal(10, 4)

Practical consequence for type choice: smallmoney only makes sense where the value range reliably stays below ±214,748 (half the storage, 4 instead of 8 bytes). If the smallmoney range is not guaranteed, money is the larger of the two currency variants. For new models whose values feed into calculations, it pays to check first whether decimal(p, s) is the better fit anyway (next section).

Rounding pitfall — money vs. decimal

The money and smallmoney types show a peculiarity in multi-step calculations: with money / 100, the result type stays money (against int, money has the higher type precedence), and so the intermediate result only has the fixed scale of four decimal places available. The rounding loss of one step thus feeds into the next. With decimal(p, s), SQL Server determines precision and scale of the result by its own rules — in division, the scale grows at first. Only the precision cap of 38 can force rounding there as well (see below).

  1: SELECT CAST(123.45678 AS money) AS [Output];                    -- 123.4568
  2: SELECT CAST(123.45678 AS money) / 100         AS [Output];      -- 1.2345  (loss starts here)
  3: SELECT CAST(123.45678 AS money) / 100 * 100   AS [Output];      -- 123.4500 (not equal to 123.4568)
  

  1: SELECT CAST(123.45678 AS decimal(10, 4)) AS [Output];                   -- 123.4568
  2: SELECT CAST(123.45678 AS decimal(10, 4)) / 100         AS [Output];     -- 1.23456800
  3: SELECT CAST(123.45678 AS decimal(10, 4)) / 100 * 100   AS [Output];     -- 123.45680000

With the money type, the result loses accuracy in the first division. The value in line 3 is no longer identical to the input value in line 1. With decimal(10, 4), the same calculation returns the input value completely — in this example. In more complex expressions, SQL Server’s precision/scale rules (cap of 38) can force rounding for decimal as well.

Key point: In multi-step reporting calculations (gross/net, tax rollups, allocation keys), the implicit money intermediate rounding accumulates errors. Using money only for storage columns and switching to decimal(p, s) for the arithmetic avoids that — see also Data quality in SQL Server // TRY_CONVERT for decimal and numeric done safely.

Converting text to money

Text input values typically come from CSV imports with amount columns. When such an nvarchar/varchar value is passed to TRY_CONVERT, the conversion expects the period as the only decimal separator. A comma in the text is not interpreted as a decimal separator but ignored like a grouping character, and more than four decimal places are rounded to the fourth. The empty string converts to 0.00. That is an important difference from the decimal target: TRY_CONVERT(decimal(19, 4), N'') returns NULL.

  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

What the lines show:

  • Line 2: A plain digit string converts as expected.
  • Line 3: The comma is not interpreted as a decimal separator but ignored: '123,45678' becomes 12345678.00 — a semantically wrong value, with no error signal at all.
  • Line 4: The period acts as the decimal separator, the fifth decimal place is rounded to the fourth.
  • Lines 5, 6: An empty string and a string consisting only of spaces convert to 0.00, not to NULL.
  • Lines 7, 8: Leading and trailing spaces are allowed and do not change the result.
  • Line 9: Scientific notation is not accepted as text. It does work as a typed number (next section). How the pattern deals with it is covered in its limitations.
  • Lines 10–15: The combination cases show observed behavior, not a documented parser grammar: in the tested inputs, a single period acts as the decimal separator, commas are ignored as grouping characters, and from the second period onwards the conversion returns NULL. This behavior is emphatically not a validation of German number notation.

Key point: For ETL processes, the most critical trait of this conversion is not the NULL but the silent wrong value: the swallowed comma turns '123,45' into 12345.00, with no error and no warning. Together with the empty string, which becomes 0.00, these are the two cases the pattern in “Safe type conversion” handles up front.

Converting typed numbers to money

If the input value arrives already typed (int, float, decimal), TRY_CONVERT converts any numeric value to money as long as it fits the value range. Values with more than four decimal places are rounded to the fourth — the value can therefore change during the conversion itself.

  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

What the lines show:

  • Line 3: 123,45678 is not a decimal number. The comma separates function arguments here: TRY_CONVERT receives the integer 123 as the input value and 45678 as the style parameter, which has no effect for money.
  • Line 4: The decimal literal 123.45678 (typed by T-SQL as numeric(8, 5)) is rounded to the fourth decimal place.
  • Line 5: 12345678E-5 is a float literal (E notation produces float) and is likewise rounded to 123.4568. As text (line 9 of the previous section), the same notation yields NULL.

Unlike the integer targets, which truncate fractional digits (see Data quality in SQL Server // TRY_CONVERT for bigint, int, smallint and tinyint done safely), conversion to money rounds. For smallmoney, the narrower range applies on top: values outside ±214,748.3647 are answered by TRY_CONVERT with NULL, where CONVERT raises a runtime error.

Safe type conversion

“Safe” in this series means fault-tolerant: no abort, NULL instead of a runtime error. Semantic correctness is not guaranteed by that — with money in particular, a “successful” conversion sometimes delivers a wrong value, because the ignored comma silently turns '123,45' into 12345.00. The pattern below eliminates exactly the two silent misconversions described above. It does not validate the complete input grammar: normalizing is not validating.

Two edge cases from the sections above need explicit handling in the import path:

  • Empty string → 0.00: If that is semantically wrong (the usual case for CSV imports), the empty string must be mapped to NULL before the TRY_CONVERT.
  • Decimal comma → ignored: If the source delivers German notation, the comma must be replaced with a period before the conversion — otherwise the silent wrong value appears.

The following example solves both edge cases directly at the money target, without a detour through other types. It assumes the source reliably delivers German notation — the limitations follow below the example:

  1: DECLARE @p_input AS nvarchar(30);
  2: SET @p_input = N'123,45678';
  3: 
  4: SELECT TRY_CONVERT( money
  5:                   , REPLACE( CASE WHEN TRIM(@p_input) = ''
  6:                                      THEN NULL
  7:                                      ELSE @p_input
  8:                                 END
  9:                            , ','
 10:                            , '.'
 11:                            )
 12:                   ) AS [Output];  -- 123.4568

Two limitations are part of this pattern:

  • Input grammar: The pattern normalizes only the decimal comma and therefore assumes a known source locale. With US notation, the same replacement would turn '1,234' (meaning 1234) into 1.234 — the normalization itself then produces the silent wrong value (see the decision aid: unknown locale → reject). Thousands-separator periods ('1.234,56') and currency symbols like the € from the opening example must be removed by an upstream cleanup step, otherwise two periods remain in the string after the comma replacement and the result is NULL. And without further arguments, TRIM removes only the regular space character (char(32)), so a string made of tabs or other Unicode whitespace is not recognized as “empty” by the pattern.
  • Scientific notation deliberately left out: As text it still yields NULL (line 9 of the test matrix). If it explicitly belongs to the input schema, it is a separate case: a float intermediate step (TRY_CONVERT(float, …) before the money target) can parse it, but works with 15–17 significant digits, while money carries up to 19 (15 integer digits plus 4 decimal places). For monetary amounts this is a parsing stopgap with a precision risk and no default — otherwise scientific notation is better treated as a data quality error than as a parsing case.

If you need the pattern across several columns of an ETL process at once, abstract it into a user-defined function fn_try_convert_money(@p_input nvarchar) — see Design Pattern // Safe Type Conversion with T-SQL.

Postgres bridge

Postgres offers two ways to store currency values — the choice is between portability and locale convenience:

  • numeric(19, 4) — the portable mapping: Covers the entire money value range, with the same scale of four decimal places and independent of the session locale. It is the portable numeric representation, not a semantically identical currency type — it carries no currency or formatting. The recommendation for ETL workloads.
  • money — the locale-bound Postgres type: Currency symbol, separators and scale follow the session locale lc_monetary. With a German locale (such as de_DE.UTF-8), the cast typically expects '1.234,56 €', with a US locale '$1,234.56' — the exact forms and locale names are system-dependent. Not recommended for portable workloads, because format drift between session locales makes the behavior fragile.

A direct TRY_CONVERT counterpart that returns the target value or NULL in a single call is missing even in Postgres 18. What is built in since Postgres 16 is fault-tolerant validation via pg_input_is_valid (more on that below). The PL/pgSQL wrapper below delivers the NULL-instead-of-exception behavior and adopts the comma normalization and the safe empty-string semantics of the pattern above right away:

  1: CREATE OR REPLACE FUNCTION fn_try_cast_numeric_19_4
  2: (
  3:     IN    p_input              text
  4: )
  5: RETURNS numeric(19, 4)
  6: LANGUAGE plpgsql
  7: IMMUTABLE
  8: AS $function$
  9: BEGIN
 10: 
 11:    IF p_input IS NULL OR TRIM(p_input) = '' THEN
 12:       RETURN NULL;
 13:    END IF;
 14: 
 15:    RETURN CAST(REPLACE(p_input, ',', '.') AS numeric(19, 4));
 16: 
 17: EXCEPTION
 18:    WHEN invalid_text_representation OR numeric_value_out_of_range THEN
 19:       RETURN NULL;
 20: END;
 21: $function$;
 22: 
 23: SELECT fn_try_cast_numeric_19_4('123,45678');   -- 123.4568
 24: SELECT fn_try_cast_numeric_19_4(' 123.45 ');    -- 123.4500
 25: SELECT fn_try_cast_numeric_19_4('1.234.5678');  -- NULL
 26: SELECT fn_try_cast_numeric_19_4('');            -- NULL

The EXCEPTION block deliberately catches the two expected error classes: invalid_text_representation for invalid representations (such as the double period after a failed normalization) and numeric_value_out_of_range for overflow. A blanket WHEN OTHERS would silently turn unexpected errors into NULL as well. And the block has a cost: according to the Postgres documentation, a BEGIN block with an EXCEPTION part is significantly more expensive than one without. For bulk imports, the built-in validation helps since Postgres 16: pg_input_is_valid(raw_value, 'numeric(19,4)'), applied to the staging column, checks convertibility set-based without a failing cast — pg_input_error_info(…) supplies the error details when needed. The wrapper remains the convenience pattern for single-value conversion.

Range and scale mapping between the two engines:

T-SQLPostgres mapping (portable)ScaleBytes (T-SQL / Postgres)
moneynumeric(19, 4)fixed-point, scale 48 / variable
smallmoneynumeric(10, 4)fixed-point, scale 44 / variable
decimal(p, s)numeric(p, s)fixed-point, scale s5–17 / variable

Cross-engine note: Postgres numeric has no fixed scale of four decimal places like T-SQL money — the specific money pitfall of intermediate rounding does not occur there, and in the example shown the value is fully preserved. But Postgres is not entirely rounding-free either: division determines a finite result scale (1::numeric / 3 returns 20 decimal places by default), so rounding is possible in principle. An unconstrained numeric is subject to very large implementation limits (up to 131,072 digits before and 16,383 digits after the decimal point). For cross-DB reporting that is a valuable consistency anchor and another argument against the Postgres money type.

Summary

  • money and smallmoney store a fixed four decimal places, and their value range can be mapped completely to decimal(19, 4) and decimal(10, 4) respectively. Multi-step calculations implicitly round every intermediate result — prefer decimal(p, s) for arithmetic.
  • With text input values, the comma is not interpreted as a decimal separator but ignored, and the empty string converts to 0.00. The safe pattern catches both (comma REPLACE plus CASE/TRIM).
  • Scientific notation yields NULL as text and works as a typed number. The pattern deliberately leaves it out — if the input schema allows it, it is handled separately via a float intermediate step with a precision trade-off.
  • Postgres: numeric(19, 4) is the portable counterpart, the Postgres-specific money type depends on lc_monetary and stays fragile for ETL. A direct try counterpart is missing, fn_try_cast_numeric_19_4 closes the gap (set-based, pg_input_is_valid helps since Postgres 16).

As a practical decision aid:

Input situationRecommended path
Period notation, plain numberdirect TRY_CONVERT(money, …)
German notation (decimal comma)normalize the comma, then convert — the safe pattern
Empty strings possible (CSV import)CASE/TRIM maps to NULL, then TRY_CONVERT
Thousands-separator periods mixed with decimal commaclean up completely beforehand or reject as a data quality error
Scientific notation in amount columnscheck whether it belongs to the input schema — otherwise treat as a data quality error instead of normalizing via float
Multi-step calculations (gross/net, allocation)decimal(p, s) instead of money for the arithmetic
Cross-engine portabilitynumeric(19, 4) in Postgres, not the Postgres money type

FAQ

Why does TRY_CONVERT(money, '123,45') return 12345.00 and not 123.45?

The comma in a text input is not interpreted by TRY_CONVERT(money, …) as a decimal separator but ignored like a grouping character. This behavior does not depend on the language setting of the SQL Server session, and a server with a German language setting changes nothing about it. For German-language input data, the comma must be replaced with a period before conversion via REPLACE(…, ',', '.') — the safe pattern above does exactly that.

How do you prevent an empty CSV field from becoming 0.00?

Map it to NULL explicitly before the conversion: CASE WHEN TRIM(@p_input) = '' THEN NULL ELSE @p_input END — exactly what the safe pattern does. Without this pre-treatment, TRY_CONVERT(money, N'') turns the empty field into a 0.00 that can no longer be distinguished from a genuine zero amount later. Note: TRIM only recognizes regular spaces (char(32)), tabs or other Unicode whitespace need extended normalization.

money or decimal — which one when?

money is fine for plain storage columns and simple conversions without multi-step arithmetic. As soon as aggregations, divisions or multiplication chains enter the picture, the implicit intermediate rounding to four decimal places produces accumulated errors — at that point decimal(p, s) with an explicitly chosen scale is the better choice. Rule of thumb: money at the system boundaries (import/export), decimal for processing. Microsoft itself advises against money for calculations and recommends decimal with at least four decimal places. For new calculation logic, decimal(p, s) is the more robust choice.

Why is (money) 123.45678 / 100 * 100 not 123.45678?

With 123.45678 / 100, the result type stays money, so the intermediate result only has four decimal places available. In the concrete example, 123.45678 / 100 is first rounded to 1.2345 (instead of staying at 1.2345678), and the subsequent multiplication by 100 then yields 123.4500 — not the input value. With decimal(p, s), the scale grows in the division, and the value is fully preserved in this case.

When should you use smallmoney instead of money?

smallmoney is capped at ±214,748.3647 and uses 4 bytes per value instead of 8. For columns with guaranteed small values (unit prices, fees, small amounts) that is the more compact choice. Whenever there is any uncertainty about the maximum possible value, switching to money has no downside other than doubling the storage — and it avoids a future type migration. For new models with calculation logic, decimal(p, s) is worth considering regardless (see “money or decimal” above).

Why does TRY_CONVERT(money, …) return NULL even though the number looks valid?

The three most common causes: more than one period in the string (typical after a comma replacement when the source contained thousands-separator periods), scientific notation as text, and a smallmoney overflow. A counter-check with TRY_CONVERT(money, …) isolates the overflow case: if it returns a value, the smallmoney range was the problem. The more dangerous cases, however, are the ones without NULL — the ignored comma and the empty string produce silent wrong values instead of a visible error signal.

Postgres counterpart for TRY_CONVERT(money, …)?

For portable workloads, numeric(19, 4) plus the PL/pgSQL wrapper fn_try_cast_numeric_19_4 from the Postgres bridge, which deliberately turns the error classes invalid_text_representation and numeric_value_out_of_range into NULL. The Postgres-specific money type exists, but hangs on the session locale via lc_monetary and stays fragile for ETL workloads.

ETL context:

TRY_CONVERT for other data types:

Fundamentals: