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?
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 .
In our demo, that is performed using three different data types conversions, it can be found that CAST is the fastest function that can be used to convert the data type of the provided value, and the PARSE function is the slowest.
The cast and convert functions provide similar functionality. They are used to convert a value from one data type to another. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
In some cases CAST performs better than PARSE, which performs better than CONVERT. In other cases, CONVERT performs better than CAST, which performs better than PARSE.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With