T-SQL Cast versus Convert

SqlSql ServerDatabaseTsql

Sql Problem Overview

What is the general guidance on when you should use CAST versus CONVERT? Is there any performance issues related to choosing one versus the other? Is one closer to ANSI-SQL?

Sql Solutions

Solution 1 - Sql

CONVERT is SQL Server specific, CAST is ANSI.

CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don't care about the extended features, use CAST.


As noted by @beruic and @C-F in the comments below, there is possible loss of precision when an implicit conversion is used (that is one where you use neither CAST nor CONVERT). For further information, see CAST and CONVERT and in particular this graphic: SQL Server Data Type Conversion Chart. With this extra information, the original advice still remains the same. Use CAST where possible.

Solution 2 - Sql

Convert has a style parameter for date to string conversions.


Solution 3 - Sql

To expand on the above answercopied by Shakti, I have actually been able to measure a performance difference between the two functions.

I was testing performance of variations of the solution to this question and found that the standard deviation and maximum runtimes were larger when using CAST.

Runtimes in milliseconds *Times in milliseconds, rounded to nearest 1/300th of a second as per the precision of the DateTime type

Solution 4 - Sql

CAST is standard SQL, but CONVERT is only for the dialect T-SQL. We have a small advantage for convert in the case of datetime.

With CAST, you indicate the expression and the target type; with CONVERT, there’s a third argument representing the style for the conversion, which is supported for some conversions, like between character strings and date and time values. For example, CONVERT(DATE, '1/2/2012', 101) converts the literal character string to DATE using style 101 representing the United States standard.

Solution 5 - Sql

Something no one seems to have noted yet is readability. Having…

    + ThatMayEvenSpan
    + MultipleLines

…may be easier to understand than…

    + ThatMayEvenSpan
    + MultipleLines
    AS SomeType

Solution 6 - Sql

CAST uses ANSI standard. In case of portability, this will work on other platforms. CONVERT is specific to sql server. But is very strong function. You can specify different styles for dates

Solution 7 - Sql

You should also not use CAST for getting the text of a hash algorithm. CAST(HASHBYTES('...') AS VARCHAR(32)) is not the same as CONVERT(VARCHAR(32), HASHBYTES('...'), 2). Without the last parameter, the result would be the same, but not a readable text. As far as I know, You cannot specify that last parameter in CAST.


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionBuddyJoeView Question on Stackoverflow
Solution 1 - SqlMatthew FarwellView Answer on Stackoverflow
Solution 2 - Sqljason saldoView Answer on Stackoverflow
Solution 3 - SqlElaskanatorView Answer on Stackoverflow
Solution 4 - SqlAbdeloihab BourassiView Answer on Stackoverflow
Solution 5 - SqlAtarioView Answer on Stackoverflow
Solution 6 - SqlRakeshPView Answer on Stackoverflow
Solution 7 - SqlkleniumView Answer on Stackoverflow