SQL Server CONVERT Function: Syntax, Style Codes and TRY_CONVERT

Plenty of old scripts still call CONVERT with a bare varchar and no style. It runs, but it cuts text at 30 characters. The SQL Server CONVERT function changes a value’s data type, and its third argument picks the format:

CONVERT(data_type[(length)], expression[, style]). So CONVERT(varchar(10), PaidOn, 101) returns 07/04/2026.

CONVERT Syntax and the Sample Payments Table

Test setup: SQL Server 2025 (17.0.1000.7) Developer, sqlcmd in VS Code, database ConvertDemo. Two text amounts are messy on purpose:

CREATE TABLE dbo.Payments (
    PaymentID  int          PRIMARY KEY,
    Customer   nvarchar(15) NOT NULL,
    Amount     money        NOT NULL,
    PaidOn     datetime     NOT NULL,
    AmountText varchar(10)  NULL
);
INSERT INTO dbo.Payments VALUES
(1, N'Hannah Brooks',  1250.75,  '2026-07-04 14:30:15', '1250.75'),
(2, N'Tyler Reed',     89.99,    '2026-03-15 09:05:00', '89.99'),
(3, N'Grace Miller',   15420.50, '2026-11-28 18:45:00', '15,420.50'),
(4, N'Marcus Bennett', 300.00,   '2026-01-09 08:00:00', '300 USD');

data_type is the target type, length is optional (30 when you skip it), and style matters for dates, money, float, xml and binary. Our SQL Server date format examples cover every layout.

Method 1: CONVERT Date Style Codes (101, 103, 112, 120, 126)

Same Payments table, Hannah’s 07/04/2026 payment through five styles:

SET NOCOUNT ON;
SELECT s.Style,
       CONVERT(varchar(23), p.PaidOn, s.Style) AS Result
FROM dbo.Payments AS p
CROSS JOIN (VALUES (1, 101), (2, 103), (3, 112),
                   (4, 120), (5, 126)) AS s(n, Style)
WHERE p.PaymentID = 1
ORDER BY s.n;

Output:

Style       Result                 
----------- -----------------------
        101 07/04/2026             
        103 04/07/2026             
        112 20260704               
        120 2026-07-04 14:30:15    
        126 2026-07-04T14:30:15

How does this work? For US readers, 101 gives MM/dd/yyyy; 103 puts the day first, and 112 drops the separators.

What this tells me: style 126 hides zero milliseconds. The full style table lives in SQL Server convert datetime to string.

Method 2: CONVERT Money Style 1 and Numbers

Same four rows, now the money column:

SET NOCOUNT ON;
SELECT Customer,
       CONVERT(varchar(12), Amount, 0) AS Style0,
       CONVERT(varchar(12), Amount, 1) AS Style1,
       CONVERT(int, Amount)            AS MoneyToInt
FROM dbo.Payments;

SELECT CONVERT(int, 1509.56e0)     AS FloatToInt,
       CONVERT(decimal(5,2), 150)  AS IntToDecimal;

sqlcmd printed:

Customer        Style0       Style1       MoneyToInt 
--------------- ------------ ------------ -----------
Hannah Brooks   1250.75      1,250.75            1251
Tyler Reed      89.99        89.99                 90
Grace Miller    15420.50     15,420.50          15421
Marcus Bennett  300.00       300.00               300
FloatToInt  IntToDecimal
----------- ------------
       1509       150.00

How does this work? Style 1 added the commas, and money rounds to int (1250.75 became 1251); float truncates (1509.56 became 1509). For padded IDs, see fixed-length strings from an int.

Method 3: Convert a String to int, decimal or date

If the text’s clean, it converts directly:

SET NOCOUNT ON;
SELECT CONVERT(int, '42')                 AS ToInt,
       CONVERT(decimal(10,2), '1250.75')  AS ToDecimal,
       CONVERT(date, '07/04/2026', 101)   AS UsText,
       CONVERT(date, '04/07/2026', 103)   AS UkText;

Result:

ToInt       ToDecimal    UsText           UkText          
----------- ------------ ---------------- ----------------
         42      1250.75       2026-07-04       2026-07-04

How does this work? '07/04/2026' with 101 and '04/07/2026' with 103 both became July 4. More in SQL Server convert string to date.

CAST vs CONVERT: Which One Should You Use?

	CAST	CONVERT
Standard	ANSI SQL	T-SQL only
Format control	None (default style)	Style codes

I pick CAST when the default layout is fine, and CONVERT the moment a human reads the output. To drop the time, convert the datetime to date.

Method 4: TRY_CONVERT Returns NULL Instead of Msg 245

Now the messy AmountText values. CONVERT fails; TRY_CONVERT (SQL Server 2012+) returns NULL:

SET NOCOUNT ON;
DECLARE @i int, @d date;
BEGIN TRY SET @i = CONVERT(int, '300 USD'); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

BEGIN TRY SET @d = CONVERT(date, '02/30/2026', 101); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

SELECT Customer, AmountText,
       TRY_CONVERT(decimal(10,2), AmountText) AS Amount
FROM dbo.Payments;

What came back:

Msg 245: Conversion failed when converting the varchar value '300 USD' to data type int.
Msg 241: Conversion failed when converting date and/or time from character string.
Customer        AmountText Amount      
--------------- ---------- ------------
Hannah Brooks   1250.75         1250.75
Tyler Reed      89.99             89.99
Grace Miller    15,420.50          NULL
Marcus Bennett  300 USD            NULL

How does this work? '15,420.50' and '300 USD' gave NULL. February 30 raised Msg 241, the same as conversion failed when converting date and/or time.

Why Did CONVERT Cut My Text at 30 Characters?

This one tripped me up:

SET NOCOUNT ON;
DECLARE @note varchar(60) =
    'Paid by Hannah Brooks, 1420 Elm Street, Austin TX';

SELECT CONVERT(varchar, @note)      AS NoLength,
       LEN(CONVERT(varchar, @note)) AS Chars;

Result:

NoLength                       Chars      
------------------------------ -----------
Paid by Hannah Brooks, 1420 El          30

Important: you don’t get an error, just Paid by Hannah Brooks, 1420 El. Size the target, and check the column data type first.

Is FORMAT() Faster Than CONVERT?

No. I timed 200,000 dates with SYSDATETIME() around each query:

SET NOCOUNT ON;
SELECT TOP (200000) DATEADD(minute,
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '2026-01-01') AS d
INTO #t FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;

DECLARE @t datetime2 = SYSDATETIME(), @x nvarchar(10);
SELECT @x = MAX(CONVERT(varchar(10), d, 101)) FROM #t;
PRINT CONCAT('CONVERT 101: ', DATEDIFF(ms, @t, SYSDATETIME()), ' ms');

SET @t = SYSDATETIME();
SELECT @x = MAX(FORMAT(d, 'MM/dd/yyyy', 'en-US')) FROM #t;
PRINT CONCAT('FORMAT: ', DATEDIFF(ms, @t, SYSDATETIME()), ' ms');

My run:

CONVERT 101: 455 ms
FORMAT: 2207 ms

CONVERT took 455 ms and FORMAT took 2,207 ms, about 5x slower. Other jobs shared this server, so the ratio is rough. To convert an integer to a string, CONVERT wins too.

Things to Keep in Mind With CONVERT (My Takeaways)

1. Give varchar a length every time.
2. Use 101 for US display, 112 or 126 for files and APIs.
3. Clean imported text with TRY_CONVERT, then check the NULLs.
4. Money rounds to int; float truncates.

Next step: PostgreSQL does this with CAST and the :: operator.

Frequently Asked Questions