Blog

This SQL Server tutorial explains how to use the CAST function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the CAST function converts an expression from one datatype to another datatype. If the conversion fails, the function will return an error. Otherwise, it will return the converted value.

TIP: Use the TRY_CAST function to return a NULL (instead of an error) if the conversion fails.

Syntax

The syntax for the CAST function in SQL Server (Transact-SQL) is:

CAST( expression AS type [ (length) ] )

Parameters or Arguments

expression

The value to convert to another datatype.

type

The datatype that you wish to convert the expression to. It can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image.

length

Optional. The length of the resulting data type for char, varchar, nchar, nvarchar, binary and varbinary.

Note

• When casting from a float or numeric to an integer, the CAST function will truncate the result. For other conversions, the CAST function will round the result.
• See also the TRY_CAST, CONVERT and TRY_CONVERT functions.

Applies To

The CAST functions can be used in the following versions of SQL Server (Transact-SQL):

• SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example

Let’s look at some SQL Server CAST functions examples and explore how to use the CAST functions in SQL Server (Transact-SQL).

For example:

SELECT CAST(14.85 AS int);
Result: 14          (result is truncated)

SELECT CAST(14.85 AS float);
Result: 14.85       (result is not truncated)

SELECT CAST(15.6 AS varchar);
Result: '15.6'

SELECT CAST(15.6 AS varchar(4));
Result: '15.6'

SELECT CAST('15.6' AS float);
Result: 15.6

SELECT CAST('2014-05-02' AS datetime);
Result: '2014-05-02 00:00:00.000'