T-SQL 101: 80 Converting data types with CAST and CONVERT

The rich set of data types in SQL Server and the T-SQL language are great. But sometimes, you need to change a value from one data type to another. The output from the commands above is:

CAST

ANSI SQL provided a way to do this.

CAST is the ANSI SQL way to convert from one data type to another. The syntax is basically:

CAST(ValueToConvert AS NewDataType)

And you can use it where you would have used an expression or constant.

In the first example above, I’ve asked to select the current system date and time, as a varchar(30). SYSDATETIME() returns the current system date and time, as a datetime2 data type. The CAST converts it to a string.

In the second example above, I wanted to select the UnitPrice of each product, but I wanted the output as a string, and with “Unit Price is” prepended to it. If I hadn’t used the CAST, I would have been adding a number to that string.

Implicit Conversions

Note that in this specific situation, T-SQL would have coped with the different data types on each side of the + sign, by “implicitly” casting the number to a string first. But that’s not always the case and you shouldn’t depend upon it. Make sure you convert data between data types explicitly.

CONVERT

T-SQL has another way to convert data from one type to another.

CONVERT is a non-standard extension to SQL. It’s an old Sybase function that’s been around as part of T-SQL ever since it started.

With CONVERT, the order of the parameters is different:

CONVERT(NewDataType, ValueToConvert)

We’ll see in the next post how CONVERT can do more than CAST, by allowing control over how the conversion occurs. And later we’ll see how to avoid errors during conversion.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2021-01-11