TRY_CONVERT for int, bigint, smallint & tinyint

A CSV import runs through without a single error message, and afterwards the quantity column shows a 0 where the source field was simply empty: TRY_CONVERT(int, N'') returns 0, not NULL. The second quirk affects already typed decimal numbers: TRY_CONVERT(int, 1234.5) does not round but cuts off — the result is 1234, not 1235.

At a glance:

The four integer types in SQL Server cover clearly tiered value ranges — from the 1-byte tinyint (0..255, the only unsigned one) up to the 8-byte bigint. When converting from the same source type, all four behave the same.
From text, TRY_CONVERT only converts integer representations. A sign and surrounding spaces are allowed, while decimal and thousands separators yield NULL regardless of locale.
Two silent edge cases: an empty string converts to 0 (usually wrong in an import), and typed decimal numbers are truncated instead of rounded. The safe pattern handles both.
In Postgres, CAST(… AS bigint) throws an exception on invalid input — a built-in try counterpart is missing. This article shows the PL/pgSQL wrapper fn_try_cast_bigint.

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(…))). The examples run without a sample database, using pure inline literals, and the Postgres side requires no particular version.

Value range of the integer types

Type choice comes before conversion. The four integer types in SQL Server differ by value range and storage size. For conversions from the same source type, the same rules apply to all four target types — what matters is whether the value fits the target range.

Data type	Min	Max	Bytes	Typical use
`bigint`	`-9 223 372 036 854 775 808`	`9 223 372 036 854 775 807`	8	Huge surrogate keys, counters beyond billions, global 64-bit IDs
`int`	`-2 147 483 648`	`2 147 483 647`	4	Standard surrogate key, quantities, counters in the 9-digit range
`smallint`	`-32 768`	`32 767`	2	Years, small quantities, legacy lookup keys
`tinyint`	`0`	`255`	1	Flags, status codes, small lookup values (unsigned)

tinyint is a Microsoft quirk and the only SQL Server integer type without a sign. A negative value cannot be converted to tinyint, the TRY_CONVERT call returns NULL. Postgres has no such type at all — the smallest integer there is smallint (signed, -32 768..32 767).

Converting text to integer

Text input values typically come from CSV, JSON and XML imports. When such an nvarchar/varchar value is passed to TRY_CONVERT, the text must represent a whole number. Digits, an optional sign and surrounding spaces are allowed. A decimal or thousands separator, however, ends the conversion with NULL, and an empty string converts to 0. That is an important difference from TRY_CONVERT(decimal(18, 2), N''), which returns NULL.

  1: SELECT TRY_CONVERT(int, NULL          ) -- NULL
  2: SELECT TRY_CONVERT(int, N'123'        ) -- 123
  3: SELECT TRY_CONVERT(int, N'123,4'      ) -- NULL
  4: SELECT TRY_CONVERT(int, N'1,234'      ) -- NULL
  5: SELECT TRY_CONVERT(int, N'123.4'      ) -- NULL
  6: SELECT TRY_CONVERT(int, N'1.234'      ) -- NULL
  7: SELECT TRY_CONVERT(int, N''           ) -- 0
  8: SELECT TRY_CONVERT(int, N' '          ) -- 0
  9: SELECT TRY_CONVERT(int, N' 123'       ) -- 123
 10: SELECT TRY_CONVERT(int, N'123 '       ) -- 123
 11: SELECT TRY_CONVERT(int, N'2147483648' ) -- NULL
 12: SELECT TRY_CONVERT(int, N'123456E-3'  ) -- NULL

  1: SELECT TRY_CONVERT(int, NULL          ) -- NULL
  2: SELECT TRY_CONVERT(int, N'123'        ) -- 123
  3: SELECT TRY_CONVERT(int, N'123,4'      ) -- NULL
  4: SELECT TRY_CONVERT(int, N'1,234'      ) -- NULL
  5: SELECT TRY_CONVERT(int, N'123.4'      ) -- NULL
  6: SELECT TRY_CONVERT(int, N'1.234'      ) -- NULL
  7: SELECT TRY_CONVERT(int, N''           ) -- 0
  8: SELECT TRY_CONVERT(int, N' '          ) -- 0
  9: SELECT TRY_CONVERT(int, N' 123'       ) -- 123
 10: SELECT TRY_CONVERT(int, N'123 '       ) -- 123
 11: SELECT TRY_CONVERT(int, N'2147483648' ) -- NULL
 12: SELECT TRY_CONVERT(int, N'123456E-3'  ) -- NULL

What the lines show:

Line 2: A plain digit string converts as expected.
Lines 3–6: In text-to-integer conversion, comma and period fail in every role, as decimal and as thousands separator alike. This conversion is locale-independent: neither SET LANGUAGE nor SET DATEFORMAT changes this result.
Lines 7, 8: An empty string and a string consisting only of spaces convert to 0, not to NULL.
Lines 9, 10: Leading and trailing spaces are allowed and do not change the result.
Line 11: The value is 1 above the int maximum of 2 147 483 647. TRY_CONVERT treats the overflow as a failed conversion and returns NULL.
Line 12: Scientific notation is not accepted as text. It does work as a typed number, which the next section shows.

Key point: An empty field in an import semantically means “unknown”, but TRY_CONVERT turns it into the business value 0: silently, with no error and no NULL. Where that is wrong (the usual case for CSV imports), the pattern in “Safe type conversion” maps the empty string to NULL beforehand.

Converting typed numbers to integer

If the input value arrives already typed, say as an integer with a different value range or as a decimal number from a pipeline calculation, the scenarios reduce to two questions: Does the value fit the target range? And what happens to fractional digits?

  1: SELECT TRY_CONVERT(int, 2147483648) -- NULL
  2: SELECT TRY_CONVERT(int,        123) -- 123
  3: SELECT TRY_CONVERT(int,     1234.5) -- 1234
  4: SELECT TRY_CONVERT(int,  123456E-3) -- 123

  1: SELECT TRY_CONVERT(int, 2147483648) -- NULL
  2: SELECT TRY_CONVERT(int,        123) -- 123
  3: SELECT TRY_CONVERT(int,     1234.5) -- 1234
  4: SELECT TRY_CONVERT(int,  123456E-3) -- 123

What the lines show:

Line 1: 2147483648 is 1 above the int maximum, the conversion fails and returns NULL.
Line 3: The typed decimal number is cut down to its integer part, 1234.5 becomes 1234. If you want rounding, apply it explicitly first: ROUND(1234.5, 0) returns 1235.0, which then converts to 1235.
Line 4: Scientific notation works as a typed float literal: 123456E-3 is 123.456, truncated to 123. As text (line 12 of the previous section), the same notation yields NULL.

Key point: TRY_CONVERT to integer silently truncates the fractional digits of decimal and float values — unlike conversion to decimal(p, s), which rounds (half away from zero). A documented exception is money as the source type, which SQL Server rounds when targeting an integer. Overflow is answered with NULL, where CAST and CONVERT raise a runtime error.

A second block shows the range boundaries per type:

  1: SELECT TRY_CONVERT(smallint,  32767) -- 32767  (smallint-MAX)
  2: SELECT TRY_CONVERT(smallint,  32768) -- NULL   (overflow)
  3: SELECT TRY_CONVERT(tinyint,     255) -- 255    (tinyint-MAX, unsigned)
  4: SELECT TRY_CONVERT(tinyint,     256) -- NULL   (overflow)
  5: SELECT TRY_CONVERT(tinyint,      -1) -- NULL   (tinyint is unsigned)

  1: SELECT TRY_CONVERT(smallint,  32767) -- 32767  (smallint-MAX)
  2: SELECT TRY_CONVERT(smallint,  32768) -- NULL   (overflow)
  3: SELECT TRY_CONVERT(tinyint,     255) -- 255    (tinyint-MAX, unsigned)
  4: SELECT TRY_CONVERT(tinyint,     256) -- NULL   (overflow)
  5: SELECT TRY_CONVERT(tinyint,      -1) -- NULL   (tinyint is unsigned)

For tinyint the conversion fails in both directions: 256 is too large, -1 violates the unsigned boundary.

Safe type conversion

“Safe” in this series means fault-tolerant: no abort, NULL instead of a runtime error. It does not automatically mean lossless — for the integer types, silent truncation is explicitly one of the losses the pattern does not prevent. Semantically wrong values (like the 0 from an empty string) are not prevented by TRY_CONVERT on its own either, which is exactly what the pattern below is for. And conversions that are outright disallowed between types are not caught by the function: they still raise an error.

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

Empty string → 0: If that is semantically wrong (the usual case for CSV imports), the empty string must be mapped to NULL before the TRY_CONVERT.
Typed decimal number → truncation: If rounding is required, a ROUND(…, 0) goes in front of the TRY_CONVERT. The alternative of targeting decimal(p, 0) does round, but does so silently as well (see FAQ).

The usage example with DECLARE and the snake_case variable convention solves the first edge case:

  1: DECLARE @p_input AS nvarchar(30);
  2: SET @p_input = N'123';
  3: 
  4: SELECT TRY_CONVERT( int
  5:                   , CASE WHEN TRIM(@p_input) = '' THEN NULL ELSE @p_input END
  6:                   ) AS [Output];

  1: DECLARE @p_input AS nvarchar(30);
  2: SET @p_input = N'123';
  3: 
  4: SELECT TRY_CONVERT( int
  5:                   , CASE WHEN TRIM(@p_input) = '' THEN NULL ELSE @p_input END
  6:                   ) AS [Output];

Two limitations are part of this pattern:

Input grammar: The pattern only catches the empty string. Thousands separators and currency symbols must be removed by an upstream cleanup step, otherwise the result stays NULL. And without further arguments, TRIM removes only the regular space character (char(32)) — a string made of tabs or other Unicode whitespace is therefore not recognized as “empty” by the pattern. If such characters can occur in the source data, the normalization has to handle them explicitly.
No rounding: Converting to an integer target type cuts off the fractional part, and the pattern does not change that. If rounding is wanted, it goes in explicitly as ROUND(…, 0) before the TRY_CONVERT.

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

Postgres bridge

On the Postgres side, the type question is answered quickly: smallint, integer and bigint cover the same value ranges as their SQL Server counterparts, only tinyint is missing (a tinyint column is usually mapped to smallint during a migration). The real difference lies in the error behavior:

CAST(… AS bigint) throws an exception on invalid input: There is no built-in try counterpart — even Postgres 18 (released 2025-09-25) ships neither a try_cast function nor the SQL/JSON syntax CAST … ON ERROR NULL.
The empty string throws an exception, too: CAST('' AS bigint) fails with invalid_text_representation. That is stricter than T-SQL, where TRY_CONVERT(int, N'') silently returns 0.

A PL/pgSQL wrapper delivers the NULL-instead-of-exception behavior. It also adopts the safe semantics of the pattern above right away: the empty string is mapped to NULL, not to 0:

  1: CREATE OR REPLACE FUNCTION fn_try_cast_bigint
  2: (
  3:     IN    p_input              text
  4: )
  5: RETURNS bigint
  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(p_input AS bigint);
 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_bigint('123');      -- 123
 24: SELECT fn_try_cast_bigint(' 123 ');    -- 123
 25: SELECT fn_try_cast_bigint('1,234');    -- NULL
 26: SELECT fn_try_cast_bigint('');         -- NULL

  1: CREATE OR REPLACE FUNCTION fn_try_cast_bigint
  2: (
  3:     IN    p_input              text
  4: )
  5: RETURNS bigint
  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(p_input AS bigint);
 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_bigint('123');      -- 123
 24: SELECT fn_try_cast_bigint(' 123 ');    -- 123
 25: SELECT fn_try_cast_bigint('1,234');    -- NULL
 26: SELECT fn_try_cast_bigint('');         -- NULL

With this, fn_try_cast_bigint('1,234') behaves like TRY_CONVERT(int, '1,234'): the call returns NULL instead of throwing an exception. For int and smallint, analogous wrappers with an adjusted return type are easy to define.

The EXCEPTION block deliberately catches the two error classes that are expected in integer conversion: invalid_text_representation for invalid number representations and numeric_value_out_of_range for overflow. A blanket WHEN OTHERS would silently turn unexpected errors into NULL as well. The EXCEPTION part is not free, though — the Postgres documentation rates a block with an exception handler as significantly more expensive than one without. For bulk imports with many expected bad values, it therefore pays to pre-check in a set-based way which values are convertible, and to cast only those. Since Postgres 16 there is purpose-built tooling for this: pg_input_is_valid('123', 'bigint') checks convertibility without triggering a failing cast, and pg_input_error_info(…) supplies the error details. The wrapper remains the convenience pattern for reusable single-value conversion.

If the source delivers formatted numbers with thousands separators, to_number with a format pattern takes over the parsing:

  1: SELECT to_number('1.234', 'FM999G999')  -- 1234  (German notation, lc_numeric = 'de_DE.UTF-8')
  2: SELECT to_number('1,234', 'FM999G999')  -- 1234  (US notation, lc_numeric = 'en_US.UTF-8')

  1: SELECT to_number('1.234', 'FM999G999')  -- 1234  (German notation, lc_numeric = 'de_DE.UTF-8')
  2: SELECT to_number('1,234', 'FM999G999')  -- 1234  (US notation, lc_numeric = 'en_US.UTF-8')

to_number is locale-dependent through the format characters G (group separator) and D (decimal separator): both follow the session variable lc_numeric, while a literal . or , in the format pattern stays locale-independent. This sets to_number apart from text-to-integer conversion in SQL Server, where the session locale plays no role. The locale names in the comments are platform-dependent, de_DE.UTF-8 and en_US.UTF-8 are for illustration. For reproducible ETL pipelines, the number format should therefore never depend implicitly on server configuration: either set lc_numeric explicitly per session or normalize the input beforehand so the format pattern works without locale characters. The return type of to_number is also numeric, so an integer column still needs a CAST afterwards.

Summary

The four integer types differ in conversion mainly by value range. Type choice: int as the default, bigint where a larger range is demonstrably needed, smallint and tinyint when their range is a lasting fit for the business data and the smaller width actually matters.
From text, TRY_CONVERT with integer targets only accepts integer representations (optionally with a sign and surrounding spaces) — separators, scientific notation and overflow yield NULL, regardless of the session locale.
The two silent traps: an empty string converts to 0, typed decimal numbers are truncated. The safe pattern maps the empty string to NULL via CASE/TRIM, rounding goes in explicitly as ROUND(…, 0) beforehand.
Postgres has no built-in try counterpart: the wrapper fn_try_cast_bigint returns NULL instead of an exception, to_number parses formatted numbers (locale-dependent via lc_numeric).

As a practical decision aid:

Input situation	Recommended path
Plain digit string, optionally signed	direct `TRY_CONVERT(int, …)`
Empty strings possible (CSV import)	safe pattern: `CASE`/`TRIM` maps to `NULL`, then `TRY_CONVERT`
Decimal numbers, rounding wanted	`ROUND(…, 0)` before the `TRY_CONVERT`
Thousands separators, source locale known	normalize deliberately (`REPLACE`), in Postgres `to_number`
Source locale unknown or mixed	do not normalize blindly — reject as a data quality error
`NULL` of unclear origin in the target column	counter-check with `TRY_CONVERT(bigint, …)`: narrow down `int` overflow (see FAQ)

FAQ

Why does TRY_CONVERT(int, N'') become 0 instead of NULL?

SQL Server treats the empty string like a 0 in text-to-integer conversion — the conversion counts as successful, there is neither an error nor a NULL. The same applies to strings consisting only of spaces. In an import that is usually not what is meant, because an empty CSV field stands for “unknown”. The safe pattern from “Safe type conversion” therefore maps the empty string to NULL beforehand via CASE/TRIM.

Why does TRY_CONVERT(int, '1,234') return NULL?

Because text-to-integer conversion works independently of the locale and knows neither decimal nor thousands separators. It expects a plain digit string, optionally with a sign and surrounding spaces. As soon as a comma or a period appears in the string, the conversion fails, regardless of SET LANGUAGE or SET DATEFORMAT. If the source reliably delivers German notation ('1.234,56'), a REPLACE cleanup helps: REPLACE(REPLACE(@p_input, '.', ''), ',', '.') turns it into '1234.56', which then converts via TRY_CONVERT(decimal(18, 2), …).

int or bigint — which type when?

int covers any range up to roughly ±2.1 billion and is the default for surrogate keys, counters and quantities. bigint steps in once the range demonstrably grows beyond that, for example with global 64-bit ID generators. smallint and tinyint make sense when their range is a lasting fit for the business data and the smaller storage width matters for the table or its indexes. Shrinking on a hunch, on the other hand, is paid for with overflow risk as the schema grows.

How do you force rounding instead of truncation when converting 1234.5?

TRY_CONVERT(int, 1234.5) returns 1234, that is truncation. For commercial rounding, an explicit ROUND goes in first: TRY_CONVERT(int, ROUND(1234.5, 0)) returns 1235. Careful: decimal(p, 0) is no strict substitute for int — SQL Server rounds silently during scale reduction (TRY_CONVERT(decimal(10, 0), 1234.5) returns 1235, no error and no NULL). A conversion that flags the loss of fractional digits as NULL on its own does not exist in SQL Server. Only SET NUMERIC_ROUNDABORT ON turns the silent rounding step into an error (default: OFF).

What if a CSV column mixes commas and periods as decimal separators?

First establish whether the source locale is known — only then is normalizing legitimate. If it is certain that the comma is the decimal separator and the period the thousands separator (German notation), REPLACE(REPLACE(@p_input, '.', ''), ',', '.') normalizes before the TRY_CONVERT. If the column delivers both notations mixed, they cannot be told apart reliably by automation, because '1,234' is ambiguous. Such values are a data quality error of the input source: reject and log them instead of guessing. When in doubt, document per record which locale the source uses.

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

The two most common causes are overflow and invisible characters. A counter-check separates the cases: if TRY_CONVERT(bigint, …) suddenly returns a value, it was int overflow. If the counter-check stays NULL as well, two causes remain: an invalid representation (a hidden separator, scientific notation, or whitespace characters beyond the regular space char(32), such as tabs or non-breaking spaces, which plain TRIM does not remove) or a value that exceeds even the bigint range.

Postgres counterpart to TRY_CONVERT(int, …)?

There is no direct counterpart: CAST(s AS int) throws an exception on invalid input, and a built-in try_cast is still missing in Postgres 18. The way to go is a PL/pgSQL wrapper like fn_try_cast_bigint from the Postgres bridge, which deliberately turns the error classes invalid_text_representation and numeric_value_out_of_range into NULL. For formatted numbers with thousands separators, to_number(s, format) is the Postgres path — locale-dependent via lc_numeric, unlike SQL Server, where the session locale plays no role in integer conversion.

ETL context:

Data quality in an ETL process
ETL vs. ELT — How to Tell Which Pattern You Actually Built — the macro view: ETL vs. ELT as an architecture decision, not the order of the letters.

TRY_CONVERT for other data types:

Fundamentals:

Data Quality // Type Conversion Basics with T-SQL — CAST, CONVERT, TRY_CAST and TRY_CONVERT compared.