Skip to content

try-convert-transact-sql.md

Latest commit

 

History

History
126 lines (85 loc) · 3.91 KB

try-convert-transact-sql.md

File metadata and controls

126 lines (85 loc) · 3.91 KB
title TRY_CONVERT (Transact-SQL)
description TRY_CONVERT returns a value cast to the specified data type if the cast succeeds; otherwise, returns NULL.
author MikeRayMSFT
ms.author mikeray
ms.reviewer randolphwest
ms.date 12/08/2025
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
f1_keywords
TRY_CONVERT_TSQL
TRY_CONVERT
helpviewer_keywords
TRY_CONVERT function
dev_langs
TSQL
monikerRange =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || >=aps-pdw-2016 || =azure-sqldw-latest || =fabric || =fabric-sqldb

TRY_CONVERT (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns NULL.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

TRY_CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Arguments

data_type

The data type into which to cast expression.

length

An optional integer that specifies the length of the target data type, for data types that allow a user specified length. The maximum value for length is 8,000 bytes.

expression

The value to cast.

style

Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.

style accepts the same values as the style parameter of the CONVERT function. For more information, see CAST and CONVERT.

The value of data_type determines the range of acceptable values. If style is NULL, then TRY_CONVERT returns NULL.

Return types

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns NULL.

Remarks

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, NULL is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

TRY_CONVERT is a reserved keyword, starting with compatibility level 110.

This function is capable of being remoted to servers that have [!INCLUDE ssSQL11] and later versions. It isn't remoted to servers that have a version earlier than [!INCLUDE ssSQL11].

Examples

A. TRY_CONVERT returns NULL

  • The following example demonstrates that TRY_CONVERT returns NULL when the cast fails.

    SELECT
    CASE WHEN TRY_CONVERT(FLOAT, 'test') IS NULL
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
END AS Result;
GO

    This query returns a result of Cast failed.

  • The following example demonstrates that the expression must be in the expected format.

    SET DATEFORMAT dmy;
SELECT TRY_CONVERT(DATETIME2, '12/31/2022') AS Result;
GO

    This query returns a result of NULL.

B. TRY_CONVERT fails with an error

The following example demonstrates that TRY_CONVERT returns an error when the cast is explicitly not permitted.

SELECT TRY_CONVERT(XML, 4) AS Result;
GO

The result of this statement is an error, because an integer can't be cast into the xml data type.

Explicit conversion from data type int to xml is not allowed.

C. TRY_CONVERT succeeds

This example demonstrates that the expression must be in the expected format.

SET DATEFORMAT mdy;
SELECT TRY_CONVERT(DATETIME2, '12/31/2022') AS Result;
GO

This query returns a result of 2022-12-31 00:00:00.0000000.

Related content