Recently I’ve been writing quite a bit about CAST and CONVERT on my Instagram page (@sql.handbook). Here I will highlight the differences in more detail.
As we know by now, both functions can be used for converting an expression of one data type to another data type. However, they do have distinct differences.
The differences:
– CAST is ANSI-SQL. CONVERT is not.
– CONVERT has a ‘style’ parameter. This allows CONVERT to change the format of the data. CAST cannot do that.
CONVERT’s ‘style’ parameter can be quite powerful. It allows you to reformat data. For example, date and time styles vary across countries. So we can use the CONVERT function to change the format of a column with data in the datetime data type into other date formats. As seen on the example below.
SELECT
Original_Date
,CONVERT(varchar, Original_Date, 101) AS US_Standard
,CONVERT(varchar, Original_Date, 104) AS German_Standard
,CONVERT(varchar, Original_Date, 111) AS Japan_Standard
FROM Date_Table
Results:
I used the CONVERT function to change the data type of the data from datetime to varchar and then applied various style formats using the values 101, 104 and 111. Which each corresponds to different country standards.
You can get a full list of the different style formats from this link: Click here
The above example is not possible with CAST.
Should I prioritise using CONVERT over CAST?
No. I recommend prioritising CAST over CONVERT if formating the data is not a requirement.
CAST is ANSI-SQL. ANSI stands for ‘American National Standards Institute’. This means that the CAST function can be found in many different platforms, not just Microsoft SQL Server. CAST can be found in RDBM’s such as PostgreSQL, MySQL and Oracle.
This is useful because if for any reason you had to move your SQL scripts from SQL Server to another RDBMS, you can do so quite easily, as the CAST function will already exist in the other systems.
Shashane Welgama's Blog 