What is the difference between Cast and Convert?
In a Transact-SQL book (Ramalho series) it is said:
CAST AND CONVERT
Implicitly converts one expression of data types to another. CAST is a synonym for CONVERT.
What do you mean by CAST is a synonym for CONVERT? What is the use orientation of one and the other? Is there a performance implication between both?
3 answers
CONVERT it is a function specific to SQL Server and CAST is default ANSI.
There is a conversion table that can be used to decide whether or not to use some conversion function for each case:
Following the information in the table, if it is necessary to perform a conversion from varchar to date we can implicitly do:
DECLARE @data DATE = '2017-06-21';
Or to convert a datetime to timestamp one must do explicitly:
DECLARE @data DATETIME = getdate();
DECLARE @hora TIMESTAMP = CAST(@data AS TIMESTAMP);
As for performance, there is no rule. For each type there is minimal variation between the two uses.
References:
Both have the same purpose. The only difference is that CAST is more aligned to the ISO standard, while CONVERT is more aligned to T-SQL.
Can be used in this way:
SELECT 9.5 AS Original, CAST(9.5 AS int) AS int,
CAST(9.5 AS decimal(6,4)) AS decimal;
SELECT 9.5 AS Original, CONVERT(int, 9.5) AS int,
CONVERT(decimal(6,4), 9.5) AS decimal;
Reference: CAST &CONVERT (TRANSACT SQL)
The two functions (CAST and CONVERT) have the same purpose, so they do the same thing, which is the conversion of the data type of an expression. There is not much difference between the two functions, as already said earlier, the CAST function is ISO standard and the CONVERT is T-SQL. The most relevant difference I see between the two functions is that in the CONVERT function, for the conversion of DATETIME data types, there is a third parameter to "choose" the desired date/time pattern, as per table available on the Microsoft website, which the CAST function does not support. In my view this is an interesting feature, especially when dealing with instructions between different systems and databases, where each stores the date in a different format. In this way, using SQL Server'S CONVERT function, you can take more advantage. Now, in terms of performance, there is no relevant difference to use one instead of the other.