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; every statement applies 1:1 to numeric.
TRY_CONVERT silently rounds fractional digits to s, but rejects surplus integer digits with NULL.
A comma as decimal separator, an empty string, and scientific notation as text all yield NULL. The safe conversion pattern works in two stages (text → float → decimal).
The Postgres counterpart is numeric(p, s) or decimal(p, s) (true synonyms); p is allowed up to 1000 (vs. 38 in SQL Server).

Prerequisite: SQL Server 2017+ (TRIM in the safe pattern; before 2017 use the LTRIM(RTRIM(…)) fallback). Postgres 12+ for the bridge.

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 implement the same SQL standard, accept the same parameters, and share the same storage layout. They are functionally identical — a column of type decimal(10, 2) and one of type numeric(10, 2) behave the same way in conversion, arithmetic, and storage.

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.

The value range follows from p and s:

`decimal(p, s)`	Min	Max	Typical use
`decimal(5, 2)`	−999.99	+999.99	percentages, small prices
`decimal(10, 2)`	−99,999,999.99	+99,999,999.99	accounting amounts
`decimal(19, 4)`	−999,999,999,999,999.9999	+999,999,999,999,999.9999	`money` counterpart with selectable scale (see TRY_CONVERT for money and smallmoney)
`decimal(38, 10)`	(max form, 28 integer + 10 fractional digits)	(max form, 28 integer + 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 — the result is NULL. 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 lever 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

  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 (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).

Take-away: A TRY_CONVERT(decimal(p, s), …) conversion can silently change the nominal value (fractional rounding) or silently discard it (integer overflow → NULL). 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”.

Text

When a value of type nvarchar/varchar is passed to TRY_CONVERT, the input must represent a number. Only the period is accepted as decimal separator. Thousands separators are not allowed. An empty string or whitespace-only string 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

  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

In detail:

Line 3: '123,456' with a comma yields NULL — the comma is not recognised as decimal separator. Unlike TRY_CONVERT for money and smallmoney, where commas are ignored.
Lines 5, 6: empty string and pure whitespace 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).

Non-Text

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

  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

In detail:

Line 3: typed decimal is rounded to s (123.456 → 123.46).
Line 4: 1234.56 as a typed decimal (internally float) 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 lever behind the two-stage pattern in the next section.

Safe type conversion

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:

  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

  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

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 // Sichere Typ-Konvertierung mit T-SQL (in German).

Postgres bridge

In multi-engine ETL pipelines the question of the Postgres counterpart comes up. The good news: Postgres is the easiest bridge in the entire TRY_CONVERT cluster, because numeric(p, s) and decimal(p, s) follow the SQL standard exactly.

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.
p up to 1000. Postgres allows precision up to 1000 (vs. 38 in SQL Server). Rarely relevant for classical ETL workloads; occasionally useful for financial calculations aggregating millions of values. Additionally, Postgres knows numeric without parameters — arbitrary precision, arbitrary scale (within the implementation limit).
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 try_cast_numeric_5_2 (p_input text)
  2:    RETURNS numeric(5, 2)
  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 CAST(REPLACE(p_input, ',', '.') AS numeric(5, 2));
 11: EXCEPTION
 12:    WHEN OTHERS THEN
 13:       RETURN NULL;
 14: END;
 15: $$;
 16: 
 17: SELECT try_cast_numeric_5_2('123,456');     -- 123.46
 18: SELECT try_cast_numeric_5_2('123456E-3');   -- 123.46
 19: SELECT try_cast_numeric_5_2('1234.56');     -- NULL
 20: SELECT try_cast_numeric_5_2('');            -- NULL

  1: CREATE OR REPLACE FUNCTION try_cast_numeric_5_2 (p_input text)
  2:    RETURNS numeric(5, 2)
  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 CAST(REPLACE(p_input, ',', '.') AS numeric(5, 2));
 11: EXCEPTION
 12:    WHEN OTHERS THEN
 13:       RETURN NULL;
 14: END;
 15: $$;
 16: 
 17: SELECT try_cast_numeric_5_2('123,456');     -- 123.46
 18: SELECT try_cast_numeric_5_2('123456E-3');   -- 123.46
 19: SELECT try_cast_numeric_5_2('1234.56');     -- NULL
 20: SELECT try_cast_numeric_5_2('');            -- NULL

Summary

In short:

decimal(p, s) and numeric(p, s) are functionally identical in SQL Server — this article uses decimal; 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).
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; the two-stage pattern is not needed.

FAQ

decimal or numeric — which one? Both implement the same SQL standard and are functionally identical in SQL Server. 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 (banker’s rounding). 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”.

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). Cross-reference: TRY_CONVERT for float and real.

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.