Conversion, Formatting and NULL Functions in T-SQL: CAST/CONVERT Styles, TRY_CONVERT, FORMAT, ISNULL, COALESCE, NULLIF, IIF and CHOOSE

Version note: The SQL queries in this post were run on SQL Server 2025 (17.0, Cumulative Update 10) with the series' Bookstore database at compatibility levels 170, 160 and 150; the PostgreSQL comparisons were run on PostgreSQL 18.7.

Guess First: Do ISNULL and COALESCE Return the Same Result?

We want to put 1.5 in place of a NULL INT variable. Both functions are described as "return the first non-NULL value":

DECLARE @i INT = NULL;
SELECT ISNULL(@i, 1.5) AS isnull_result, COALESCE(@i, 1.5) AS coalesce_result;

isnull_result  coalesce_result
-------------  ---------------
1              1.5

(1 rows affected)

ISNULL returned 1. The result type is the type of the first argument, INT; when 1.5 was converted to it, the fractional part was cut off. No error, no warning. COALESCE picked the argument type with the highest data type precedence, DECIMAL, and returned 1.5. In post 06 we saw the string side of the same difference, ISNULL(@code, 'unknown') being cut to "unk". In this post we go through conversion and NULL functions in detail: data type precedence and implicit conversion, CONVERT style codes, the TRY_ family, the cost of FORMAT, the three differences between ISNULL and COALESCE, NULLIF, IIF, CHOOSE, GREATEST and LEAST. The examples again use the database of Bookstore, the online bookstore used as the shared example across this site's series.

Data Type Precedence and Implicit Conversion

When values of two different types meet in an expression, SQL Server converts one to the other's type. The data type precedence list decides which: the lower-precedence one is converted to the higher one. Numeric types come before string types:

SELECT 1 + '1' AS int_plus_str, '1' + '1' AS str_plus_str, 1.5 + '1' AS dec_plus_str;

int_plus_str  str_plus_str  dec_plus_str
------------  ------------  ------------
2             11            2.5

1 + '1' became an addition because the string was converted to INT; '1' + '1' is concatenation because both are strings. A string that cannot be converted gives an error: SELECT 1 + 'a' stops with "Conversion failed when converting the varchar value 'a' to data type int." (Msg 245). This rule works more sneakily in WHERE. customers.phone is a VARCHAR column; let us compare it with a number:

SELECT customer_id FROM dbo.customers WHERE phone = 905550179553;

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

The numeric literal is NUMERIC(12,0) with higher precedence, so SQL Server tried to convert every value in the column to a number and stopped at the first phone in "+90 555 ..." form. Converting the column also makes the index unusable; the situation we saw with CONVERT_IMPLICIT in post 07. The rule: write the literal or parameter in the column's type.

Implicit conversion also has a silent side. An empty string converts to a number and a date without an error:

SELECT CAST('' AS INT) AS empty_to_int, CAST('' AS DATE) AS empty_to_date, TRY_CAST('' AS INT) AS try_empty_int;

empty_to_int  empty_to_date  try_empty_int
------------  -------------  -------------
0             1900-01-01     0

An empty quantity from a CSV becomes 0, an empty date becomes January 1, 1900, and even TRY_CAST counts this as "success". With DECIMAL, however, CAST('' AS DECIMAL(10,2)) raises an error (Msg 8114); the same empty value produces three different outcomes depending on the type. PostgreSQL is stricter here: ''::int fails with "invalid input syntax for type integer". For incoming data, turn empty strings into NULL with NULLIF(x, '') first, then convert.

CONVERT and Style Codes

CAST is standard SQL, CONVERT is T-SQL-specific and its third argument is a style code. The most common styles from date to text:

DECLARE @d DATETIME2(0) = '2026-11-03 14:05:09';
SELECT CONVERT(VARCHAR(30), @d, 112) AS s112, CONVERT(VARCHAR(30), @d, 23) AS s23, CONVERT(VARCHAR(30), @d, 120) AS s120,
       CONVERT(VARCHAR(30), @d, 126) AS s126, CONVERT(VARCHAR(30), @d, 104) AS s104, CONVERT(VARCHAR(30), @d, 103) AS s103,
       CONVERT(VARCHAR(30), @d, 101) AS s101, CONVERT(VARCHAR(30), @d, 108) AS s108;

s112      s23         s120                 s126                 s104        s103        s101        s108
--------  ----------  -------------------  -------------------  ----------  ----------  ----------  --------
20261103  2026-11-03  2026-11-03 14:05:09  2026-11-03T14:05:09  03.11.2026  03/11/2026  11/03/2026  14:05:09

112 for file names and sortable keys, 23 and 120 for logs and reports, 126 for JSON and API output that needs ISO 8601, 104 for the day.month.year form used in Turkey and Germany. 103 and 101 use the same separators, but day and month swap places: November 3 is 03/11 in British form and 11/03 in American form. In the other direction, from text to date, the style describes the text's format:

SELECT CONVERT(DATE, '03.11.2026', 104) AS p104, CONVERT(DATE, '03/11/2026', 103) AS p103, CONVERT(DATE, '03/11/2026', 101) AS p101,
       CONVERT(DATE, '20261103', 112) AS p112;

p104        p103        p101        p112
----------  ----------  ----------  ----------
2026-11-03  2026-11-03  2026-03-11  2026-11-03

The same '03/11/2026' became November 3 or March 11 depending on the style. Without a style, the session's language setting decides: in a us_english session CONVERT(DATE, '03.11.2026') returned 2026-03-11. If the style and the text do not match you get an error: CONVERT(DATE, '2026-11-03', 104) stops with "Conversion failed when converting date and/or time from character string." (Msg 241). If you receive date text from outside, know its format and always write the style explicitly.

TRY_CAST, TRY_CONVERT, TRY_PARSE and PARSE

A supplier exports a price list from Excel; price and start date arrive as text in a staging table:

DROP TABLE IF EXISTS dbo.supplier_price_staging;
CREATE TABLE dbo.supplier_price_staging (
    line_no    INT          NOT NULL PRIMARY KEY,
    isbn       VARCHAR(20)  NOT NULL,
    price_text VARCHAR(30)  NULL,
    valid_from VARCHAR(30)  NULL
);
INSERT INTO dbo.supplier_price_staging (line_no, isbn, price_text, valid_from) VALUES
    (1, '9798900000268', '420,00',    '03.11.2026'),
    (2, '9798900000398', '1.234,50',  '03.11.2026'),
    (3, '9798900000602', '1450.00',   '2026-11-03'),
    (4, '9798900000589', '890,00 TL', '31.11.2026'),
    (5, '9798900000015', '',          NULL),
    (6, '9798900000022', 'N/A',       '04.11.2026');

The TRY_ family returns NULL instead of an error when a value cannot be converted. TRY_CAST and TRY_CONVERT convert with SQL Server's own rules; TRY_PARSE takes a culture name and uses .NET parsing rules:

SELECT line_no, price_text,
       TRY_CAST(price_text AS DECIMAL(10,2)) AS try_cast_price,
       TRY_PARSE(price_text AS DECIMAL(10,2) USING 'tr-TR') AS try_parse_tr,
       valid_from,
       TRY_CONVERT(DATE, valid_from, 104) AS try_convert_104
FROM dbo.supplier_price_staging
ORDER BY line_no;

line_no  price_text  try_cast_price  try_parse_tr  valid_from  try_convert_104
-------  ----------  --------------  ------------  ----------  ---------------
1        420,00      NULL            420.00        03.11.2026  2026-11-03
2        1.234,50    NULL            1234.50       03.11.2026  2026-11-03
3        1450.00     1450.00         145000.00     2026-11-03  NULL
4        890,00 TL   NULL            NULL          31.11.2026  NULL
5                    NULL            NULL          NULL        NULL
6        N/A         NULL            NULL          04.11.2026  2026-11-04

Read the table carefully. TRY_CAST did not recognize the Turkish comma format. TRY_PARSE read the Turkish format correctly, but turned "1450.00" in line 3 into 145,000: in Turkish the dot is the thousands separator. Had this row been NULL, you would notice at once; a price a hundred times too high can slip through checks. "890,00 TL" and "N/A" correctly became NULL, and so did the empty string. On the date side, line 4 is NULL because there is no November 31, and line 3 in ISO form is NULL because it does not match style 104.

The lesson: TRY_ functions find rows that "did not convert", not rows that "converted wrongly". If you are sure a source uses a single format, choose the one method that fits it; if formats are mixed, first determine which format each row follows with a rule (does it contain a comma, a dot), write rows that did not convert or look suspicious to a separate error table, and have a person approve them before importing.

PARSE is the error-raising version of TRY_PARSE: PARSE('N/A' AS DECIMAL(10,2) USING 'tr-TR') stops with "Error converting string value 'N/A' into data type numeric using culture 'tr-TR'." (Msg 9819). It also reads month names by culture: PARSE(N'3 Kasım 2026' AS DATE USING 'tr-TR') returned 2026-11-03. When I wrote the same expression without the N prefix, the text appeared as "3 Kasim 2026" in the error message; one more example of the N prefix trap from post 19. As a TRY_ counterpart, PostgreSQL has had pg_input_is_valid('N/A', 'numeric(10,2)') since version 16, and it returns false.

FORMAT: Culture-Aware Formatting and Its Cost

FORMAT turns a value into text with a .NET format string and a culture:

DECLARE @p DECIMAL(10,2) = 1234.5;
SELECT CONVERT(VARCHAR(20), CAST(@p AS MONEY), 1) AS money_style1, CONVERT(VARCHAR(20), @p) AS plain,
       FORMAT(@p, 'N2', 'tr-TR') AS fmt_tr, FORMAT(@p, 'C', 'tr-TR') AS fmt_currency, FORMAT(@p, '#,0.00', 'de-DE') AS fmt_de,
       FORMAT(CAST('2026-11-03' AS DATE), 'dd MMMM yyyy dddd', 'tr-TR') AS fmt_date_tr;

money_style1  plain    fmt_tr    fmt_currency  fmt_de    fmt_date_tr
------------  -------  --------  ------------  --------  ------------------
1,234.50      1234.50  1.234,50  ₺1.234,50     1.234,50  03 Kasım 2026 Salı

This is the shortest way to get "1.234,50" in a report. The price is CPU: according to Microsoft Learn, FORMAT depends on the presence of the .NET CLR, and the formatting is done separately for each row's value. I compared the two approaches on a 200,000-row price history:

DROP TABLE IF EXISTS dbo.price_history;
CREATE TABLE dbo.price_history (
    history_id INT           NOT NULL PRIMARY KEY,
    price      DECIMAL(10,2) NOT NULL
);
INSERT INTO dbo.price_history (history_id, price)
SELECT value, 200 + (value % 1250) + 0.5
FROM GENERATE_SERIES(1, 200000);

SET STATISTICS TIME ON;
SELECT MAX(FORMAT(price, 'N2', 'tr-TR')) AS with_format FROM dbo.price_history;
SELECT MAX(CONVERT(VARCHAR(20), price)) AS with_convert FROM dbo.price_history;
SET STATISTICS TIME OFF;

SQL Server parse and compile time:
   CPU time = 116 ms, elapsed time = 116 ms.
with_format
-----------
999,50
 SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 242 ms.
SQL Server parse and compile time:
   CPU time = 2 ms, elapsed time = 2 ms.
with_convert
------------
999.50
 SQL Server Execution Times:
   CPU time = 24 ms,  elapsed time = 24 ms.

Timings vary from machine to machine and from run to run, so the absolute values mean little; across three runs the FORMAT query used between 8 and 15 times the CPU of the CONVERT query. Look at the ratio, not the numbers. On a screen with a few hundred rows this difference does not matter; in an export of millions of rows it does. If you do not need FORMAT, you can get the same look with CONVERT's money style and the TRANSLATE from post 19:

SELECT TOP (3) title, list_price,
       FORMAT(list_price, 'N2', 'tr-TR') AS with_format,
       TRANSLATE(CONVERT(VARCHAR(20), CAST(list_price AS MONEY), 1), ',.', '.,') AS with_translate
FROM dbo.books
WHERE list_price >= 1000
ORDER BY book_id;

title                     list_price  with_format  with_translate
------------------------  ----------  -----------  --------------
The Pragmatic Programmer  1150.00     1.150,00     1.150,00
Refactoring               1150.00     1.150,00     1.150,00
Design Patterns           1450.00     1.450,00     1.450,00

Style 1 produces the format with a comma as the thousands separator and a dot as the decimal separator; TRANSLATE swaps the two. Still, the right place is usually the application: return the number from the API as a number and let Angular's DecimalPipe or .NET's ToString("N2", culture) format it for the user's culture.

ISNULL and COALESCE: Type, Nullability and Number of Evaluations

We saw the first difference at the start of the post: ISNULL's result takes the first argument's type, while COALESCE's is chosen by type precedence. The second difference is whether the result is considered nullable. Let us create a new table with SELECT INTO and look at the column definitions:

DROP TABLE IF EXISTS dbo.null_type_test;
SELECT customer_id,
       ISNULL(city, N'-') AS city_isnull,
       COALESCE(city, N'-') AS city_coalesce
INTO dbo.null_type_test
FROM dbo.customers;
SELECT c.name, TYPE_NAME(c.user_type_id) AS type_name, c.max_length, c.is_nullable
FROM sys.columns AS c
WHERE c.object_id = OBJECT_ID('dbo.null_type_test')
ORDER BY c.column_id;
DROP TABLE dbo.null_type_test;

name           type_name  max_length  is_nullable
-------------  ---------  ----------  -----------
customer_id    int        4           0
city_isnull    nvarchar   200         0
city_coalesce  nvarchar   200         1

Both produce the same value, but SQL Server treated the ISNULL column as NOT NULL and the COALESCE column as nullable. This matters when you want to include a computed column in a primary key, or want a column in a table created with SELECT INTO to be NOT NULL.

The third difference is the number of evaluations. The standard defines COALESCE(a, b) as shorthand for CASE WHEN a IS NOT NULL THEN a ELSE b END, and SQL Server expands it that way; if a is a subquery, it can run twice. Let us sum each book's sold quantity, with 0 for books that never sold, in two ways:

SET STATISTICS IO ON;
SELECT SUM(x) FROM (SELECT COALESCE((SELECT SUM(oi.quantity) FROM dbo.order_items AS oi WHERE oi.book_id = b.book_id), 0) AS x FROM dbo.books AS b) t;
SELECT SUM(x) FROM (SELECT ISNULL((SELECT SUM(oi.quantity) FROM dbo.order_items AS oi WHERE oi.book_id = b.book_id), 0) AS x FROM dbo.books AS b) t;
SET STATISTICS IO OFF;

Table 'Worktable'. Scan count 73, logical reads 1518, ...
Table 'order_items'. Scan count 2, logical reads 10, ...
Table 'books'. Scan count 1, logical reads 3, ...
Table 'books'. Scan count 1, logical reads 3, ...
Table 'Worktable'. Scan count 0, logical reads 0, ...
Table 'order_items'. Scan count 1, logical reads 5, ...

Both return 822. But the COALESCE query scanned order_items twice and did 1,518 logical reads on a temporary worktable; the ISNULL query finished with a single scan and 5 logical reads. The plan shows COALESCE expanded to CASE WHEN [Expr1002] IS NOT NULL THEN [Expr1005] ELSE (0) END, with the subquery computed in two separate branches; ISNULL is a single isnull([Expr1002],(0)). With a subquery or another expensive expression, prefer ISNULL, or move the subquery into a join or CROSS APPLY. If you write portable code, COALESCE is the standard one; PostgreSQL has no ISNULL.

NULLIF, IIF, CHOOSE, GREATEST and LEAST

NULLIF(a, b) returns NULL if the two values are equal, otherwise a. Its most common use is preventing division by zero (post 06). Cancellation rate per customer, NULL instead of an error for a customer without orders:

SELECT c.customer_id, c.first_name,
       COUNT(o.order_id) AS orders,
       SUM(IIF(o.status = 'Cancelled', 1, 0)) AS cancelled,
       CAST(100.0 * SUM(IIF(o.status = 'Cancelled', 1, 0)) / NULLIF(COUNT(o.order_id), 0) AS DECIMAL(5,1)) AS cancel_pct
FROM dbo.customers AS c
LEFT JOIN dbo.orders AS o ON o.customer_id = c.customer_id
WHERE c.customer_id IN (1, 3, 38)
GROUP BY c.customer_id, c.first_name
ORDER BY c.customer_id;

customer_id  first_name  orders  cancelled  cancel_pct
-----------  ----------  ------  ---------  ----------
1            Ada         39      2          5.1
3            Grace       33      0          .0
38           Zeynep      0       0          NULL

IIF and CHOOSE are shorthands added to T-SQL in 2012, and the optimizer expands them to CASE. The plan shows it clearly:

SET SHOWPLAN_TEXT ON;
GO
SELECT IIF(list_price >= 1000, 'premium', 'standard') AS band,
       CHOOSE(DATEPART(QUARTER, listed_on), 'Q1', 'Q2', 'Q3', 'Q4') AS listed_quarter
FROM dbo.books;
GO
SET SHOWPLAN_TEXT OFF;

  |--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [sqlseries_ext_21].[dbo].[books].[list_price]>=(1000.00) THEN 'premium' ELSE 'standard' END, [Expr1003]=CASE WHEN datepart(quarter,[sqlseries_ext_21].[dbo].[books].[listed_on])=(1) THEN 'Q1' ELSE CASE WHEN datepart(quarter,[sqlseries_ext_21].[dbo].[books].[listed_on])=(2) THEN 'Q2' ELSE CASE WHEN datepart(quarter,[sqlseries_ext_21].[dbo].[books].[listed_on])=(3) THEN 'Q3' ELSE CASE WHEN datepart(quarter,[sqlseries_ext_21].[dbo].[books].[listed_on])=(4) THEN 'Q4' ELSE NULL END END END END))

CHOOSE became four nested CASE expressions, with datepart written again in each branch; if the first argument is an expensive or nondeterministic expression (for example a number derived from NEWID()), it can be recomputed in every branch. At the edges it behaves like CASE too:

SELECT CHOOSE(0, 'a', 'b') AS idx_0, CHOOSE(3, 'a', 'b') AS idx_3, CHOOSE(NULL, 'a', 'b') AS idx_null, CHOOSE(2.9, 'a', 'b', 'c') AS idx_2_9,
       IIF(NULL = NULL, 'equal', 'not equal') AS iif_null;

idx_0  idx_3  idx_null  idx_2_9  iif_null
-----  -----  --------  -------  ---------
NULL   NULL   NULL      b        not equal

An out-of-range or NULL index returns NULL; 2.9 was cut to 2 when converted to INT, so "b" came back. IIF(NULL = NULL, ...) returned "not equal", because the condition is UNKNOWN and IIF treats it like FALSE (the three-valued logic of post 06).

GREATEST and LEAST arrived with SQL Server 2022, as we saw in post 17. For this post I tested their NULL behaviour again at three compatibility levels:

SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME();
SELECT GREATEST(1, NULL, 3) AS g_mixed, GREATEST(NULL, NULL) AS g_all_null, LEAST(CAST(NULL AS INT), 2) AS l_mixed,
       GREATEST('9', 10) AS g_types, GREATEST(N'istanbul', N'İzmir') AS g_collation;
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160;
SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME();
SELECT GREATEST(1, NULL, 3) AS g_mixed, GREATEST(NULL, NULL) AS g_all_null, LEAST(CAST(NULL AS INT), 2) AS l_mixed;
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;
SELECT GREATEST(1, NULL, 3) AS g_mixed_150;
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 170;

compatibility_level
-------------------
170
g_mixed  g_all_null  l_mixed  g_types  g_collation
-------  ----------  -------  -------  -----------
3        NULL        2        10       İzmir
compatibility_level
-------------------
160
g_mixed  g_all_null  l_mixed
-------  ----------  -------
3        NULL        2
g_mixed_150
-----------
3

The same at all three levels: NULL arguments are ignored, and the result is NULL only if all of them are NULL. The GREATEST page on Microsoft Learn states the same rule for SQL Server 2022 and later. My test ran on the SQL Server 2025 engine at level 160; I did not run it on an actual SQL Server 2022 installation. GREATEST('9', 10) returned 10, because '9' was converted to INT by type precedence; the comparison was numeric, not textual. String comparison follows the collation. We saw in post 17 that MySQL returns NULL on a single NULL argument.

Function Summary and PostgreSQL Counterparts

I ran the expressions in the PostgreSQL column in my own PostgreSQL 18 container:

One detail in PostgreSQL: to_number('1.234,50', '9G999D99') returned 1.23 under the default C locale, because the G and D separators are read from the lc_numeric setting. That is PostgreSQL's counterpart of TRY_PARSE turning "1450.00" into 145,000.

In Production

Load incoming data into a staging table as text first, do the conversion there in a set-based way and separate rows that did not convert into an error table. "Did not convert" (NULL) and "was empty" ('' or NULL) are different cases; the first is an error, the second missing data. Then there is "converted wrongly": when the culture or style is chosen wrongly, you end up with a 145,000 TRY book or a campaign starting on March 11. To catch it, add a check query that compares converted values against sensible ranges (price 1–10,000 TRY, date within a year of today).

Using FORMAT for a few hundred rows on a report screen is fine; for exports and batch work use CONVERT or leave formatting to the application. When choosing between ISNULL and COALESCE, think about the type explicitly: in ISNULL(column, default) the default's type must fit the column's. If there is a COALESCE with a subquery, check in the plan whether the same table is read twice.

Common Pitfalls

• ISNULL(int_column, 1.5) returning 1 and ISNULL(varchar(5), 'long text') being cut; the result type is the first argument's.
• Comparing a VARCHAR column with a number, converting every row to a number (Msg 8114, Msg 245) and losing the index.
• An empty string becoming 0 in INT and 1900-01-01 in DATE; apply NULLIF(x, '') first.
• TRY_PARSE(... USING 'tr-TR') turning the dotted "1450.00" into 145,000; TRY_ does not catch values that convert wrongly.
• Converting date text without a style; '03/11/2026' becomes November 3 or March 11 depending on style and language.
• Using FORMAT on millions of rows; its CPU cost is a multiple of CONVERT's.
• Putting a subquery inside COALESCE and running it twice.

A Small Experiment

★☆☆ Convert the prices in supplier_price_staging with TRY_PARSE, using the tr-TR culture for values containing a comma and en-US for the rest. Is line 3 1450.00 now? List the rows that did not convert in a separate query.

★★☆ From the orders table, produce a daily order summary with the date in "03.11.2026" form using CONVERT style 104 and revenue in "1.234,50" form using the TRANSLATE trick. Write the same query with FORMAT and compare the CPU ratio with SET STATISTICS TIME ON.

★★★ Write a "last activity" column that returns each book's latest review date, or its latest order date if it has no reviews, or listed_on if it has neither: first with a COALESCE of two subqueries, then with OUTER APPLY and GREATEST/COALESCE. Compare the logical reads of the two versions with SET STATISTICS IO ON.

In the next post we move on to math, system and metadata functions: ROUND and integer division, random selection with NEWID, the difference between SCOPE_IDENTITY and @@IDENTITY, @@ROWCOUNT and querying the schema through catalog views.