Know When to Use Cast versus Convert

By: Kris Wenzel   |   Updated: March 6, 2022  
Works With: 

In this article, we will be exploring the CAST and CONVERT functions to understand whether there are any significant differences worth knowing when we want to data from one type to another.

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

Let’s read further. We will see whether there really is a difference between these two functions in this post.

The examples you’ll find in this lesson are based on the 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?

The CAST and CONVERT functions 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 functions 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 jogs your memory regarding the CAST and CONVERT functions.  If not, then read my blog Data Type Conversion Functions to know all the details.

Similarities

In many ways CAST and CONVERT are similar.   Both are used to convert data from one type to another.  Thought 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 in performance, according to Nakul Vachhrajani’s post, there isn’t.  In fact, Nakul shows that CAST is really implemented internally via CONVERT!

Differences

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

CONVERT differences lie in that it accepts an optional style parameter that 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 other’s’ experiences.

Related Links:

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

    1. The difference is you cannot do formatting using the CAST function, whereas, you can perform formatting using the CONVERT function.

  1. This is exactly the perspective I was looking for. Knowing which functions are part of ANSI Spec is very important to me as I try to be more marketable so I’m all about keeping that info present in your posts! Thanks ✌?

  2. I’d use CONVERT every time where the data type is date or datetime, sometimes combined with CAST. Consider this example converting between float and datetime –
    DECLARE @test FLOAT
    SET @test = 20140114
    SELECT @test, CONVERT(DATETIME, CAST(CAST(@test AS INTEGER) AS varchar))

    Ouch!

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}