Difference between CAST and CONVERT

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:

Continue reading “Difference between CAST and CONVERT”