Data quality in SQL Server // TRY_CONVERT for decimal and numeric done safely

Published Mar 1, 2024 · Updated Aug 31, 2026
by

Anyone who has watched a price import turn '123.45 €' into a NULL instead of the expected decimal number knows the drill: TRY_CONVERT(decimal(5, 2), '123,45') returns NULL, because a comma isn’t accepted as a decimal separator. And even with the comma gone, TRY_CONVERT(decimal(5, 2), '1234.56') is also NULL — this time because of one integer digit too many.

At a glance:

  • decimal(p, s) and numeric(p, s) are functionally identical in SQL Server — this article uses decimal throughout, and every statement applies 1:1 to numeric.
  • TRY_CONVERT silently rounds fractional digits to s, but rejects surplus integer digits with NULL.
  • In the text-to-decimal conversion, a comma as decimal separator, an empty string, and scientific notation all yield NULL. The two-stage pattern (text → float → decimal) catches the notation — as a parsing workaround with a precision limit, not as a universal route.
  • The Postgres counterpart is numeric(p, s) or decimal(p, s) (true synonyms). p is allowed up to 1000 (vs. 38 in SQL Server) — a TRY_CONVERT counterpart is missing there, though, and the article shows the wrapper.

Prerequisite: TRY_CONVERT has existed since SQL Server 2012. The safe pattern uses TRIM and therefore needs SQL Server 2017+ (before that, use the LTRIM(RTRIM(…)) fallback). The Postgres examples require no particular version.

Content

Decimal and Numeric — functionally identical

SQL Server provides two data types for storing exact decimal numbers: decimal(p, s) and numeric(p, s). Both are synonyms with the same precision/scale rules and the same conversion behaviour. They are functionally identical — a column of type decimal(10, 2) and one of type numeric(10, 2) behave the same way in conversion and arithmetic.

This article uses decimal throughout. Every statement applies 1:1 to numeric.

Choosing precision and scale

decimal(p, s) has two parameters:

  • precision (p) — the total number of decimal digits the type can store. Allowed values are 1 to 38.
  • scale (s) — the number of digits to the right of the decimal point. Allowed values are 0 to p.

In concrete terms: decimal(5, 2) stores five digits in total — three before and two after the decimal point. That makes it immediately clear why 1234.56 fails and 123.456 gets rounded.

The value range follows from p and s:

decimal(p, s)MinMaxTypical use
decimal(5, 2)−999.99+999.99percentages, small prices
decimal(10, 2)−99,999,999.99+99,999,999.99accounting amounts
decimal(19, 4)−999,999,999,999,999.9999+999,999,999,999,999.9999money-like: same digit count, different value range (see TRY_CONVERT for money and smallmoney)
decimal(38, 10)(max form, 28 integer and 10 fractional digits)(max form, 28 integer and 10 fractional digits)financial mathematics, scientific values

Rule of thumb: integer digits + fractional digits ≤ p. If a value has more integer digits than p − s permits, conversion is impossible — TRY_CONVERT then returns NULL, while a CAST/CONVERT raises an error. Fractional digits, on the other hand, are silently rounded without error.

Rounding vs. integer overflow

The asymmetry between fractional and integer digits is the central trap when working with decimal:

  1: SELECT TRY_CONVERT(decimal(5, 2), '123.456')   -- 123.46
  2: SELECT TRY_CONVERT(decimal(5, 2), '1234.56')   -- NULL
  3: SELECT TRY_CONVERT(decimal(5, 2), '12345.6')   -- NULL
  4: SELECT TRY_CONVERT(decimal(5, 2),  1234.56)    -- NULL
  5: SELECT TRY_CONVERT(decimal(5, 2),  123.456)    -- 123.46

  • Fractional digits are rounded to s places — commercially, so a 5 rounds away from zero: 10.645 → 10.65, -10.645 → -10.65 (half away from zero, not banker’s rounding). That happens silently, without error, without NULL.
  • Integer overflow yields NULL — both for text input (lines 2, 3) and for typed numbers (line 4). The NULL is specific to TRY_CONVERT: a CAST/CONVERT raises a runtime error at the same point.

Key point: A TRY_CONVERT(decimal(p, s), …) conversion can silently change the input value (fractional rounding) or silently signal the conversion with NULL (integer overflow). In ETL pipelines the latter is often the more dangerous trap: a NULL in the target column looks like “source delivered no value”, but actually means “value too large for the scale”.

Converting text to decimal

When a value of type nvarchar/varchar is passed to TRY_CONVERT, the input must represent a number. As the decimal separator, this text-to-decimal conversion accepts only the period. Thousands separators are not allowed. An empty string or a string consisting only of spaces converts directly to NULL — an important difference from float and money, where empty strings convert to 0.

  1: SELECT TRY_CONVERT(decimal(5, 2), NULL        )  -- NULL
  2: SELECT TRY_CONVERT(decimal(5, 2), N'123'      )  -- 123.00
  3: SELECT TRY_CONVERT(decimal(5, 2), N'123,456'  )  -- NULL
  4: SELECT TRY_CONVERT(decimal(5, 2), N'123.456'  )  -- 123.46
  5: SELECT TRY_CONVERT(decimal(5, 2), N''         )  -- NULL
  6: SELECT TRY_CONVERT(decimal(5, 2), N' '        )  -- NULL
  7: SELECT TRY_CONVERT(decimal(5, 2), N' 123.456' )  -- 123.46
  8: SELECT TRY_CONVERT(decimal(5, 2), N'123.456 ' )  -- 123.46
  9: SELECT TRY_CONVERT(decimal(5, 2), N'1234.56'  )  -- NULL
 10: SELECT TRY_CONVERT(decimal(5, 2), N'123456E-3')  -- NULL

What the lines show:

  • Line 3: '123,456' with a comma yields NULL — the comma is not recognised as decimal separator. Unlike Data quality in SQL Server // TRY_CONVERT for money and smallmoney done safely, where commas are ignored.
  • Lines 5, 6: an empty string and a string consisting only of spaces become NULL. Different from float, real, money and smallmoney, where the result would be 0.
  • Lines 7, 8: leading and trailing spaces are trimmed and don’t prevent conversion.
  • Line 9: '1234.56' has too many integer digits for decimal(5, 2) (max p − s = 3 integer digits) → NULL.
  • Line 10: scientific notation '123456E-3' as text returns NULL. That changes when the number is passed as a typed value (see next section).

Converting typed numbers to decimal

When the number is already passed as a typed value (int, float, decimal), TRY_CONVERT can convert any numeric value to decimal — as long as the integer digits fit:

  1: SELECT TRY_CONVERT(decimal(5, 2), NULL     )  -- NULL
  2: SELECT TRY_CONVERT(decimal(5, 2), 123      )  -- 123.00
  3: SELECT TRY_CONVERT(decimal(5, 2), 123.456  )  -- 123.46
  4: SELECT TRY_CONVERT(decimal(5, 2), 1234.56  )  -- NULL
  5: SELECT TRY_CONVERT(decimal(5, 2), 123456E-3)  -- 123.46

What the lines show:

  • Line 3: a typed decimal is rounded to s (123.456 → 123.46).
  • Line 4: the decimal literal 1234.56 — typed by T-SQL as numeric(6, 2) — exceeds the p − s = 3 integer-digit budget → NULL.
  • Line 5: scientific notation does work as a typed number (123456E-3 → 123.456 → rounded to 123.46). This difference from the text variant is the basis for the two-stage pattern in the next section.

Safe type conversion

“Safe” in this series means: fault-tolerant — no abort, NULL instead of a runtime error. It does not automatically mean lossless. That is exactly why the limitations below belong to this pattern.

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

  • Scientific notation as text ('12345E-3') yields NULL. Workaround: convert to float first (which tolerates the notation), then to decimal (which enforces precision and scale).
  • An empty string is converted to NULL directly by TRY_CONVERT(decimal, …) — but once the two-stage pattern routes through float, that no longer holds: TRY_CONVERT(float, '') yields 0. The empty string therefore has to be mapped to NULL explicitly before the float step.

The following example handles both edge cases together — as a parsing workaround, not a universal route (the limitations follow right below the example):

  1: DECLARE @p_input AS nvarchar(30);
  2: SET @p_input = N'123,456';
  3: 
  4: SELECT TRY_CONVERT( decimal(5, 2)
  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];  -- 123.46

Two limitations are part of this pattern:

  • Input grammar. The pattern normalises only the decimal separator (comma → period). Thousands separators ('1.234,56') and currency symbols — like the € from the opening example — have to be removed by an upstream cleansing step, otherwise the result stays NULL. Whitespace is narrowly defined too: without further arguments, TRIM removes only regular spaces, not tabs or other Unicode whitespace.
  • Precision limit. float (without parameters float(53), IEEE 754 double precision) is an approximate data type with 15–17 significant decimal digits — not every decimal number can be represented exactly. For the values shown in this article, the detour produces the same decimal(5, 2) result as a direct conversion. For monetary amounts and other business-exact values with more significant digits, however, the float intermediate step can change the value. When exactness matters and no scientific notation occurs, the direct route TRY_CONVERT(decimal(p, s), …) without the intermediate step is the better choice.

If the pattern is needed across many columns in an ETL flow, abstract it into a user-defined function fn_try_convert_decimal(@p_input nvarchar, @p_precision int, @p_scale int) — see Design Pattern // Safe Type Conversion with T-SQL.

Postgres bridge

For multi-engine pipelines, decimal is the easiest bridge in the entire TRY_CONVERT cluster: numeric(p, s) and decimal(p, s) fully cover the SQL standard in Postgres and extend it in a few places.

Three differences against SQL Server:

  • Synonyms, not just identical. In Postgres, numeric(p, s) and decimal(p, s) are true synonyms — the column definition is always stored as numeric internally. In SQL Server the original identifier is preserved.
  • Declared precision up to 1000. Explicitly declared numeric(p, s) types allow p up to 1000 (vs. 38 in SQL Server). Since Postgres 15, the declared scale may even be negative (−1000 to 1000) — it then rounds to digits left of the decimal point. An unconstrained numeric without parameters is subject to much larger implementation limits: up to 131,072 integer digits and 16,383 fractional digits.
  • Scientific notation as text works. Postgres CAST('123456E-3' AS numeric(5, 2)) yields 123.46. The two-stage pattern via float is therefore not needed in Postgres — the notation is accepted directly.

Postgres, however, has no built-in try_cast (not in Postgres 18 either, released 2025-09-25). A PL/pgSQL wrapper provides the NULL-instead-of-exception behaviour:

  1: CREATE OR REPLACE FUNCTION fn_try_cast_numeric_5_2
  2: (
  3:     IN    p_input              text
  4: )
  5: RETURNS numeric(5, 2)
  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(5, 2));
 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_5_2('123,456');     -- 123.46
 24: SELECT fn_try_cast_numeric_5_2('123456E-3');   -- 123.46
 25: SELECT fn_try_cast_numeric_5_2('1234.56');     -- NULL
 26: SELECT fn_try_cast_numeric_5_2('');            -- NULL

The EXCEPTION block deliberately catches the two expected error classes — invalid number representation (invalid_text_representation) and value-range overflow (numeric_value_out_of_range). A blanket WHEN OTHERS would silently turn unexpected errors into NULL as well. One cost note on top: according to the Postgres documentation, a block with an EXCEPTION clause is significantly more expensive than one without. For large data volumes with many expected errors, set-based pre-checks are worth a look.

Summary

  • decimal(p, s) and numeric(p, s) are functionally identical in SQL Server — this article uses decimal, and every statement applies 1:1 to numeric.
  • TRY_CONVERT(decimal(p, s), …) silently rounds fractional digits, but rejects surplus integer digits with NULL. The asymmetry is the central trap in ETL pipelines.
  • For text inputs: comma → NULL, empty string → NULL, scientific notation → NULL. For typed numbers, scientific notation works. Hence the two-stage pattern (text → float → decimal) — as a parsing workaround with a float precision limit, not for exact values with many significant digits.
  • Postgres counterpart: numeric(p, s) or decimal(p, s) — same value ranges up to p = 38, beyond that up to p = 1000. Scientific notation as text is accepted directly, so the two-stage pattern is not needed there.

As a practical decision aid:

Input situationRecommended route
Period notation, scale knowndirect TRY_CONVERT(decimal(p, s), …)
Comma as decimal separatornormalise the decimal separator, then TRY_CONVERT
Scientific notation as texttwo-stage pattern — only if the precision risk is acceptable
Monetary amounts and other business-exact valuesno float intermediate step — validate the input upstream
ETL error diagnosisstore raw and converted value separately, log the error class

FAQ

decimal or numeric — which one? 

Both are synonyms in SQL Server and functionally identical. Which identifier you use is a matter of convention: decimal is more common in T-SQL communities, numeric in ANSI standard documentation. Tools like the SSMS designer often default to decimal.

Why does TRY_CONVERT(decimal(5, 2), '123,45') return NULL?

Because the comma is not recognised as a decimal separator in SQL Server — TRY_CONVERT expects only a period. For German-format input data, replace the comma with a period before conversion. The safe conversion pattern above handles that with REPLACE.

Does TRY_CONVERT(decimal, …) round or reject?

Both — depending on whether the problem sits on the fractional side or the integer side. Fractional digits are silently rounded to s (half away from zero). Integer overflow yields NULL. This asymmetry matters in ETL pipelines: a NULL in the target column for decimal doesn’t necessarily mean “source delivered no value” — it can also mean “source had too many integer digits”.

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

Usually integer overflow is behind it: the number is syntactically correct but has more than p − s integer digits. A counter-check with a larger precision separates the cases. If TRY_CONVERT(decimal(38, 2), …) returns a value, it was overflow. If the counter-check stays NULL as well, the number representation itself is invalid. For ETL checks it pays to store the raw and the converted value separately and to log the error class alongside.

How do I safely convert scientific notation ('12345E-3') to decimal?

The two-stage pattern from the “Safe type conversion” section — first to float (which tolerates the notation as text), then to decimal (which enforces precision/scale). The float intermediate step is a parsing workaround: with more than roughly 15 significant digits it can change the value (see the limitations in that section). Cross-reference: Data quality in SQL Server // TRY_CONVERT for float and real done safely.

TRY_CAST or TRY_CONVERT for decimal?

Both provide the NULL-instead-of-error behaviour and are interchangeable for this use case. TRY_CONVERT additionally offers the optional style parameter — it plays no role for decimal, but it does for date and money conversions. If you want to stay close to the SQL standard, use TRY_CAST. All four functions are compared in Data Quality // Type Conversion Basics with T-SQL.

Postgres counterpart for TRY_CONVERT(decimal, …)?

CAST(s AS numeric(p, s)) or CAST(s AS decimal(p, s)) — Postgres makes no distinction. For NULL-instead-of-exception behaviour you need a PL/pgSQL wrapper (Postgres has no built-in try_cast as of 18.0). Postgres is also more liberal with text inputs: scientific notation is accepted directly.

ETL context:

TRY_CONVERT for other data types:

Fundamentals: