CAST vs CONVERT - Which function to use?
Overview
CAST and CONVERT are two functions used to convert an expression from a datatype to another datatype in SQL Server.
CAST() is an ANSI-standard function, which means it’s being used not only in MS SQL Server but across different RDBMS.
CONVERT() does the same job but it’s tied to MS SQL Server only, and a small syntax difference is that it accepts the convert style, which allows you to define the output formats, while CAST() does not.
Syntax
Generic format for CAST function:
CAST(expression AS data_type [(length )])
Generic format for CONVERT function:
CONVERT(data_type [(length)], expression [, style])
Which one to use?
There seems to be no difference in the performance of the two functions, so it’s just a matter of preferences to choose which function to use.
You can choose to use CAST when you work with multiple relational databases and the syntaxes are pretty standardized, easy to remember.
On the other hand, if you need to specify the output styling, use CONVERT, even though the style is optional.
The CAST function is often used to preserve decimal values and places while converting between decimal/numeric values. The CONVERT function cannot do this.
Example
- Use the CAST function to convert a string valiue to a datetime value:
SELECT CAST('2019-09-10' AS datetime);
The result will be:
2019-09-10 00:00:00.000
- Switch to CONVERT:
SELECT CONVERT(datetime, '2019-09-10');
Same output:
2019-09-10 00:00:00.000
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download for iOS
