Querying Databases Using Transact-SQL/

...

/

Casting and Conversion

Casting and Conversion

Learn about casting and converting a value to a different data type in T-SQL.

We'll cover the following...

When we assign a value of one type to a column that stores data of another type, MS SQL Server tries to perform a conversion and cast the value to the needed type. Let’s consider a table with one column of type NVARCHAR:

CREATE TABLE ReferenceTable
(
    Data NVARCHAR(100)
);

If we try to insert a numeric value into the column above, an automatic conversion to NVARCHAR takes place:

CREATE DATABASE ReferenceDatabase;
USE ReferenceDatabase;

CREATE TABLE ReferenceTable
(
    Data NVARCHAR(100)
);

INSERT INTO dbo.ReferenceTable (Data)
VALUES (56), (23), (68); -- We put INT values here

-- INT values are converted and are inserted into NVARCHAR column
SELECT * FROM dbo.ReferenceTable;
Automatic conversion from INT to NVARCHAR

However, not all conversions will take place automatically. In some situations, we might need to use explicit conversion.

The CAST() function

The easiest way to cast an object of one type to another is to use the CAST() function. It has the following syntax:

 ...