CAST and CONVERT are two functions that explicitly converts an expression from one data type to another data type.

Syntax:

-- CAST Syntax:
CAST ( expression AS data_type [ ( length ) ] )

-- CONVERT Syntax:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Let us see a quick example of CAST and CONVERT as below.

Select cast(getdate() as varchar(max))
Select convert(varchar(max),getdate(),101)
Select convert(varchar(max),getdate(),102)

If we observe the difference, CAST has only a one form of way, however, CONVERT is feasible to convert to different results as we define the style of the format. There are many different style options, and you can refer those in “standard date CONVERT formats” section of ready reckoner date post.

In fact, CAST is changing as CONVERT internally while executing the query. We can simply look at the execution plan and understand this behavior as below screen shot.

SQL Server Data Type Conversion Chart:

The below chart is a beautiful way of representing the Data type conversion in SQL Server.

Credit: https://www.microsoft.com/en-us/download/confirmation.aspx?id=35834

Points to ponder:

• CAST is an ANSI SQL Standard, however, CONVERT is a SQL Server specific.
• CAST is single form result function, other hand, CONVERT function results as style format defined.
• There are NO major notable difference observed in performance.

If you enjoyed this blog post, feel free to share it with your friends!