TRY_CONVERT // Converting data to money, smallmoney

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 — what this article covers:

  • Range and storage of money (±9.22e14, 8 bytes) and smallmoney (±214,748.3647, 4 bytes), and the decimal(p, s) equivalence.
  • The rounding pitfall money / 100 * 100 != money — and when decimal(p, s) is the better data type.
  • TRY_CONVERT behaviour for text inputs (a comma is ignored, an empty string becomes 0) and for typed values (a comma is parsed as the style-parameter separator).
  • A safe conversion pattern using CASE/TRIM/REPLACE and the Postgres counterpart (numeric(19, 4) for portability, money with the lc_monetary caveat).

Prerequisite: SQL Server 2017+ (TRIM is used in the safe pattern; for 2012–2016 use LTRIM(RTRIM(...)) as a fallback). No sample dataset required — every example uses inline literals.

Content

Range and Storage

SQL Server offers two data types for storing currency values. Both fix four decimal places and are essentially identical, in terms of the values stored, to a decimal(p, 4) with a matching total digit count. The internal storage representation differs, but for the business interpretation of the value that difference is not relevant.

TypeMinMaxBytesDecimal placesdecimal equivalent
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 picking the type: smallmoney is worth it only when the range stays comfortably below ±214,748 (half the storage, 4 bytes instead of 8). When in doubt pick money — or jump straight to decimal(p, s) when calculation accuracy matters (next section).

Rounding Pitfall — money vs. decimal

The money and smallmoney data types have a special behaviour in multi-step calculations: SQL Server implicitly converts the intermediate result back to four decimal places after each step before continuing. With decimal(p, s), the scale grows during division instead of being rounded — the nominal value is preserved.

  1: SELECT CAST(123.45678 AS money) AS [Output];                    -- 123.4568
  2: SELECT CAST(123.45678 AS money) / 100         AS [Output];      -- 1.2345  (Verlust ab hier)
  3: SELECT CAST(123.45678 AS money) / 100 * 100   AS [Output];      -- 123.4500 (nominal nicht gleich 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

Using money, the first division loses precision. The values in line 1 and line 3 are not nominally equal. Using decimal(10, 4), the same calculation returns the original value in full.

Take-away for picking the type: In multi-step reporting calculations (gross/net, tax allocation, distribution keys) the implicit money rounding produces accumulated errors. This error can be avoided by using money only for storage columns and switching to decimal(p, s) for arithmetic — see also TRY_CONVERT // Converting data to decimal or numeric.

The result of converting a value to money also depends on the type in which the number is passed. There is a distinction between passing expression as a text value (nvarchar/varchar) and as an already typed number (int, float, decimal).

Text

When the value is passed as nvarchar/varchar, the input must represent a number. Only the period is accepted as a decimal separator. A comma in the input is ignored — not interpreted as a decimal separator. This behaviour is locale-independent and differs from the locale-aware parsing common in application layers.

Since decimal numbers in German-language text files typically use the comma as a decimal separator, the comma must be replaced by a period before conversion. If the input contains both a decimal period and commas as thousands separators, that replacement produces a value with multiple periods and therefore NULL — the test list below illustrates the range.

Leading and trailing spaces are stripped by TRY_CONVERT and do not prevent conversion. An empty string is not converted to NULL but to 0 — an edge case that the safe pattern below catches explicitly. If the input has more than four decimal places, TRY_CONVERT rounds to the fourth.

  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 show some nonsensical combinations of period and comma placement to illustrate the thousands- and decimal-separator behaviour. Short version: a single period acts as the decimal separator, any number of commas in front of it are ignored; two periods or a period after a period yield NULL.

In lines 5 and 6, the input is an empty string and a string containing only whitespace, respectively. TRY_CONVERT converts both to the number 0. In an ETL context that is almost always semantically wrong — an empty string means „no value delivered”, not „the value is zero”. So the empty string must be mapped to NULL explicitly before conversion.

No Text

If the number is already passed as a typed value (int, float, decimal), the function can convert any numeric value to money. Values with more than four decimal places are rounded to the fourth — so the nominal input value may already change during the conversion.

  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

Line 3 deserves attention: this is not a decimal number. The comma is parsed as the style-parameter separator of TRY_CONVERT — the function receives the integer 123 as expression and the (for money irrelevant) value 45678 as the style. In line 4, expression is passed as a typed decimal with fractional digits (internally float); conversion then proceeds as expected.

For smallmoney the narrower range applies as well: values outside ±214,748.3647 yield NULL under TRY_CONVERT (instead of throwing an exception as CONVERT would).

Safe Type Conversion

The two sections above have shown two edge cases that have to be handled explicitly in the import path:

  • An empty string is converted to 0. When that is semantically wrong (the default case in CSV imports), the empty string has to be mapped to NULL before the TRY_CONVERT.
  • A comma as decimal separator is silently ignored — '123,45' is read as 12345, not 123.45. When the source delivers German notation, the comma has to be replaced with a period before the TRY_CONVERT.

The following example handles both edge cases together:

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

If the pattern is needed across many columns in an ETL flow, abstract it into a user-defined function fn_try_convert_money(@p_input nvarchar) — see Design Pattern // Sichere Typ-Konvertierung mit T-SQL (in German).

Postgres Bridge

Postgres offers two ways to store currency values — the choice is a portability vs. convenience tradeoff:

  • numeric(19, 4) is the direct portable counterpart to T-SQL money. Same range, same scale, locale-independent. The recommendation for ETL workloads.
  • money is a Postgres-specific type that takes its appearance (currency symbol, thousands and decimal separators, scale) from the session locale lc_monetary. With de_DE.UTF-8, Postgres expects the string '1.234,56 €' on cast; with en_US.UTF-8, it expects '$1,234.56'. Not recommended for portable workloads — format drift between session locales makes the behaviour fragile.

Postgres has no built-in try_cast (as of Postgres 18.0, 2025-09-25). A PL/pgSQL wrapper with an EXCEPTION block delivers the NULL-instead-of-exception behaviour of TRY_CONVERT:

  1: CREATE OR REPLACE FUNCTION try_cast_numeric_19_4 (p_input text)
  2:    RETURNS numeric(19, 4)
  3:    LANGUAGE plpgsql
  4:    IMMUTABLE
  5: AS $$
  6: BEGIN
  7:    IF p_input IS NULL OR TRIM(p_input) = '' THEN
  8:       RETURN NULL;
  9:    END IF;
 10:    RETURN REPLACE(p_input, ',', '.')::numeric(19, 4);
 11: EXCEPTION
 12:    WHEN invalid_text_representation OR numeric_value_out_of_range
 13:    THEN
 14:       RETURN NULL;
 15: END;
 16: $$;

Range and scale mapping between the two engines:

T-SQLPostgres counterpart (portable)Format / scaleBytes (T-SQL / Postgres)
moneynumeric(19, 4)fixed-point, 4 decimal places8 / variable (typ. 16)
smallmoneynumeric(10, 4)fixed-point, 4 decimal places4 / variable (typ. 12)
decimal(p, s)numeric(p, s)fixed-point, s decimal places5–17 / variable

Cross-engine note: Postgres numeric has no implicit intermediate rounding to a fixed scale — the rounding pitfall from the previous section does not exist in Postgres numeric. numeric operates with arbitrary precision (up to 131,072 significant digits) and expands the scale during division. For cross-DB reporting that is a valuable consistency anchor — and another argument against the Postgres-money type.

Summary

Converting a value to the money data type involves several edge cases: empty strings are converted to 0, numbers in scientific notation passed as text yield NULL, a comma in text is ignored rather than treated as a decimal separator. The pattern shown above catches these cases and ensures that empty inputs arrive as NULL (instead of 0) in the target system.

The money rounding pitfall plays a minor role in plain conversion. As soon as an ETL process performs calculations with money values — aggregations, allocations, gross/net rollups — the implicit 4-decimal intermediate rounding has to be accounted for, and decimal(p, s) is often the better target type.

Take-away in four bullets:

  • money and smallmoney fix four decimal places. Intermediate results in multi-step calculations are rounded implicitly — for arithmetic-heavy workloads, prefer decimal(p, s).
  • TRY_CONVERT(money, '') yields 0.00, not NULL. Map empty strings to NULL explicitly before conversion.
  • A comma in text is ignored, not parsed as a decimal separator. '123,45' yields 12345.00. For German-language inputs, replace the comma with a period.
  • Postgres counterpart: numeric(19, 4) is the portable recommendation; the Postgres-specific money type is locale-dependent (lc_monetary) and fragile in ETL. No built-in try_cast — write your own PL/pgSQL wrapper.

FAQ

Why does TRY_CONVERT(money, '123,45') return 12345.00 and not 123.45? The comma in a text input is ignored by TRY_CONVERT(money, …) — it is not parsed as a decimal separator. This behaviour is locale-independent, so it stays the same even on a server with a German language setting. For German-language input data, the comma has to be replaced with a period before conversion via REPLACE(…, ',', '.') (see the safe pattern above).

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: use money at the system boundaries (import/export), decimal for processing.

Why is (money) 123.45678 / 100 * 100 not 123.45678? SQL Server implicitly converts the result of each step in a money calculation back to four decimal places before continuing. Concretely, 123.45678 / 100 is first rounded to 1.2345 (rather than staying at 1.2345678), and the subsequent multiplication by 100 then yields 123.4500 — not the original value. With decimal(p, s), the scale grows during division instead of being rounded, and the nominal value is fully preserved.

When should I 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.

Postgres counterpart for TRY_CONVERT(money, …)? For portable workloads use numeric(19, 4) plus a PL/pgSQL wrapper with an EXCEPTION block that returns NULL on invalid_text_representation or numeric_value_out_of_range (instead of raising) — full implementation in the Postgres bridge above. The Postgres-specific money type exists but is locale-dependent via lc_monetary and not recommended for ETL workloads.

Related Posts

1 thought on “TRY_CONVERT // Converting data to money, smallmoney”

  1. Pingback: Data quality in an ETL process - Just another SQL blog

Comments are closed.