Search⌘ K
Home
Courses
Querying Databases Using Transact-SQL

Casting and Conversion

Explore how to perform explicit data type casting and conversion in T-SQL using CAST and CONVERT functions. Understand automatic versus explicit conversions, handle conversion errors, and use TRY_CONVERT to avoid query failures when casting incompatible types.

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:

 ...