What is the Difference Between Cast and Convert?

In this article, we’re going to explore the CAST and CONVERT functions to understand whether there are any significant differences worth knowing when we want to convert data from one type to another.

Since CAST and CONVERT can be used interchangeably in most situations, you may be wondering if one is better than the other.

Read on! We’ll see whether there really is a difference between CAST and CONVERT in this post.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  You can get started using these free tools using my Guide Getting Started Using SQL Server.

Are Cast and Convert Different?

CAST and CONVERT are both used to convert data from one data type to another, and it is no coincidence they share the same entry in MSDN.

Here is an example using both CAST and CONVERT in the same statement:

SELECT CAST ('10' as int) * 20,
       CONVERT (int, '10') * 20

In both cases, we’re converting from the text value ’10’ to its integer representation.

Hopefully this example provides jogs your memory regarding CAST and CONVERT.  If not, read my blog Data Type Conversion Functions to know all the details.

Similarities with CAST and CONVERT

In many ways, CAST and CONVERT are similar. Both are used to convert data from one type to another. Though their syntax is different, both functions are able to convert values from one formation to another.

Anything you can do with CAST, you can do with CONVERT.  If you’re wondering whether there is a difference in performance, according to Nakul Vachhrajani’s post, there isn’t. In fact, Nakul shows that CAST is really implemented internally via CONVERT!

Differences Between CAST and CONVERT

CAST is part of the ANSI-SQL specification; whereas, CONVERT is not.  In fact, CONVERT is SQL implementation specific.

CONVERT differences lie in that that accepts an optional style parameter which is used for formatting.

For example, when converting a DateTime datatype to Varchar, you can specify the resulting date’s format, such as YYYY/MM/DD or MM/DD/YYYY.

SELECT CONVERT(VARCHAR,GETDATE(),101) as MMDDYYYY,
       CONVERT(VARCHAR,GETDATE(),111) as YYYYMMDD

The numbers shown in red are the style formatter. There are many style formats you can use. The complete list is here.

Should I Use CAST or Convert?

Unless you have some specific formatting requirements you’re trying to address during the conversion, I would stick with using the CAST function. There are several reasons I can think of:

1. CAST is ANSI-SQL compliant; therefore, more apt to be used in other database implementation.
2. There is no performance penalty using CAST.
3. I think CAST is easier to read, and since it is part of the ANSI specification, your non-SQLServer DBA thinks so too!

What do you think? I would love to know. Please post in the comments which function you would rather use and why. This is a great way for us to learn from each others’ experiences.

The post What is the Difference Between Cast and Convert? appeared first on Essential SQL.