T-SQL Cast versus Convert

Question

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

User · Accepted Answer

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   EDIT   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

User · Answer

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

User · Answer

Convert has a style parameter for date to string conversions   http   msdn microsoft com en-us library ms187928 aspx

User · Answer

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

User · Answer

Something no one seems to have noted yet is readability   Having     CONVERT SomeType      SomeReallyLongExpression       ThatMayEvenSpan       MultipleLines            may be easier to understand than     CAST SomeReallyLongExpression       ThatMayEvenSpan       MultipleLines     AS SomeType

User · Answer

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

User · Answer

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     Times in milliseconds  rounded to nearest 1 300th of a second as per the precision of the DateTime type

[sql] T-SQL Cast versus Convert

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to tsql