Do You Want to Learn SQL?

Take our free eight day course, where I'll teach you in simple to understand English all you need to get started learning SQL.

Start Now
Tutorial

What is the Difference between Cast versus Convert?

Kris Wenzel

·

·

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.

Table of contents

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:

13 responses to “What is the Difference between Cast versus Convert?”
  1. walkman herry

    clean and good explanation . thank for explanation in very easy way! .

    Reply
  2. anonymous

    This is the wordiest article to say “There’s no difference”.

    Reply
    1. Sablama

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

      Reply
  3. bryant

    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 ✌?

    Reply
    1. Kris Wenzel

      Thanks!

      Reply
  4. Pedro Henrique Priuli

    An insignificant difference, but if used in large quantities, CAST might be better, if it counts bits

    Reply
    1. Kris Wenzel

      Performance wise, there is no difference. See this article testing the performance between CAST and CONVERT.

      Now, regarding aesthetics, I tend to go with CAST, as it is easier to read.

      Reply
      1. LEVE that

        yes , but it is not what we are showing them

        Reply
  5. Raj

    Clean and clear explaination

    Reply
    1. Kris Wenzel

      Thanks!

      Reply
  6. MaxDdos from Snoopy Alien

    I always try out CAST firstly and in case it doesn’t work, I test the query with the CONVERT function.

    Reply
  7. Chrisalis

    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!

    Reply
    1. JayKay from Northern Germany

      I don't get it. This works as well using CAST exclusively.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

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

Table Of Contents
  1. Table of contents
  2. Are Cast and Convert Different?
  3. Similarities
  4. Differences
  5. Should I use CAST or Convert?
  6. Related Links:
More from the blog

MySQL PostgreSQL SQLite SQL Server