MSDN Documentation

Documentation > SQL Server > Reference > Functions > Scalar Functions > Conversion Functions

SQL Server Conversion Functions

This section provides detailed information about the scalar conversion functions available in SQL Server. These functions are used to convert an expression from one data type to another. Understanding and using these functions correctly is crucial for effective data manipulation and querying.

Introduction to Conversion Functions

Data type conversion is a common operation in SQL Server. You might need to convert a string to a number, a date to a string, or a numeric value to a different precision. SQL Server offers a set of built-in scalar functions that simplify these conversions. These functions generally take an expression as input and return a value of the specified target data type. It's important to be aware of potential data loss or errors that can occur during conversion if the source data cannot be accurately represented in the target data type.

List of Scalar Conversion Functions

CAST (Transact-SQL)

The CAST function converts an expression from one data type to another. It's a standard SQL function. 

CAST ( expression AS data_type [ ( length ) ] )

Arguments

Name Description
expression Any valid Transact-SQL expression that returns or converts to a value.
data_type The target data type to which the expression will be converted.
length Optional. For character data types, the length of the target data type. Ignored for numeric and date/time types.

Example: 

SELECT CAST('123.45' AS DECIMAL(10, 2));

CONVERT (Transact-SQL)

The CONVERT function is similar to CAST but offers more control, especially for date and time formats, by using style codes. 

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

Arguments

Name Description
data_type The target data type.
length Optional. For character data types, the length of the target data type.
expression The expression to convert.
style Optional. An integer value that specifies the format for date and time conversions.

Example (Converting date to a specific string format): 

SELECT CONVERT(varchar, GETDATE(), 101); -- mm/dd/yyyy format

TRY_CAST (Transact-SQL)

TRY_CAST attempts to cast an expression to a specified data type. If the conversion is not valid, it returns NULL instead of an error. This is useful for robust data handling. 

TRY_CAST ( expression AS data_type [ ( length ) ] )

Example: 

SELECT TRY_CAST('abc' AS INT); -- Returns NULL

TRY_CONVERT (Transact-SQL)

Similar to TRY_CAST, TRY_CONVERT attempts a conversion using the syntax of CONVERT. It returns NULL on failure, allowing for safe data ingestion. 

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

Example: 

SELECT TRY_CONVERT(DATE, '2023-13-01'); -- Returns NULL

PARSE (Transact-SQL)

PARSE converts character data to a value of a requested data type using locale-specific information. This function is useful when dealing with data that might have cultural variations in formatting. 

PARSE ( string_value AS data_type [ USING culture ] )

Arguments

Name Description
string_value The string to parse.
data_type The target data type.
culture Optional. The culture to use for parsing (e.g., 'en-US', 'fr-FR'). Defaults to the server's culture.

Example: 

SELECT PARSE('1,234.56' AS DECIMAL(10, 2) USING 'en-US');

TRY_PARSE (Transact-SQL)

TRY_PARSE attempts to parse a string value into a requested data type. It returns NULL if the parsing fails, making it safer than PARSE for potentially invalid input. 

TRY_PARSE ( string_value AS data_type [ USING culture ] )

Example: 

SELECT TRY_PARSE('January 1, 2023' AS DATE USING 'en-US');

Common Usage Scenarios

See Also