What’s the difference between Cast and Convert?

Asked

Viewed 12,428 times

7

In a Transact-SQL (Ramalho Series) book it is said:

CAST AND CONVERT

It implicitly converts one expression of data types to another. CAST is synonymous with CONVERT.

What do you mean by CAST is synonymous with CONVERT? What is the usage orientation of both? There is a performance implication between the two?

sql database sql-server tsql cast

  • @Henry will be the same?

  • 1

    Useful link

  • Possible duplicate of Difference between CAST and CONVERT in Mysql

  • 3

    Marconi and @Ivanferrer The ends are the same. I believe that the means are different and, even if everything is the same, I think it is important that you keep the question, Marconi. Even if it is closed. Then, when someone goes looking for this about SQL Server will fall here and will see that the answer lies in the other question.

  • @Marconi: CONVERT has more options than CAST. // Care should be taken when using the CAST function, because in some cases it uses session settings to decide how the conversion will be. In the CONVERT function the conversion rule is defined.

3 answers

7


CONVERT is a specific function of SQL Server and CAST is standard ANSI.

There is a conversion table that can be used to decide whether or not to use some conversion function for each case:

Tabela de conversão

Following the information in the table, if a conversion of varchar for date we can implicitly do:

DECLARE @data DATE = '2017-06-21';

Or to convert a datetime for timestamp should be made explicitly:

DECLARE @data DATETIME = getdate();
DECLARE @hora TIMESTAMP = CAST(@data AS TIMESTAMP);

As for performance, there is no rule. For each type there is a minimum variation between the two uses.

References:

  • Explicit conversion is CONVERT and the implicit is CAST? I didn’t get the image.

  • The image is what you can use, or not, without using any function. It does not differentiate one from the other. It is just a curiosity

  • 1

    I didn’t understand the table either @Sorack! I could explain?

  • @Marconi’s conversion table indicates if you can do the assignment directly or if you have to use some function, for example declare @data date = '2017-06-21'

  • @Is there any way to add these types of conversions to the answer? I think the table was very intuitive, but you can improve the answer =D a little more

  • @Marconi found the following explanation

  • 1

    @Added marconi

Show 2 more comments

1

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 previously stated, the CAST function is ISO standard and 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 shown on the Microsoft website, that the CAST function does not support. In my view this is an interesting feature, especially when we deal with instructions between different systems and databases, where each stores the date in a different format. This way, using the CONVERT function of SQL Server, you can take more advantage. Now, in terms of performance, there is no relevant difference to use one rather than another.

https://docs.microsoft.com/pt-br/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

1

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

Browser other questions tagged sql database sql-server tsql cast

You are not signed in. Login or sign up in order to post.