The CAST and CONVERT functions are conversion functions. We use these functions to convert a data type to another data type. Of course, every data can not be converted to each data type. For example, if the value of a data with a data type varchar is “123”, it can be converted to an int data type. However, if the value of the data is “mytext”, the data cannot be converted to int data type.
Difference Between CAST and CONVERT:
The CAST Function is an ANSI standard. It is also supported by some other databases.
Convert is a SQL Server-specific function. It is more flexible and more capable than CAST.
In general, I suggest you choose to use CAST. If CAST does not meet your needs, you can choose CONVERT. The CONVERT function is especially useful when you want to display the datetime data type in a format you want.
The syntax of the two functions is different. You can see these differences in the following examples.
Let’s make examples for a better understanding of CAST and CONVET Functions.
Example:
We will do our examples on a dataset as follows. Original data types are as follows. The database we use is AdventureWorks2012.
| [Name] | varchar(50) |
| [ListPrice] | money |
| [ModifiedDate] | datetime |
| [Size] | nvarchar(5) |
|
1 2 3 4 5 6 7 |
SELECT TOP (5) [Name] ,[ListPrice] ,[ModifiedDate] ,[Size] FROM [AdventureWorks2012].[Production].[Product] WHERE ListPrice>10 AND Size is not null |
Convert ListPrice column(data type is money) to varchar and int data type:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT TOP (5) [Name] ,CONVERT(varchar(10), ListPrice) AS ListPrice ,CAST(ListPrice AS varchar(10)) AS ListPrice ,CONVERT(int, ListPrice) AS ListPrice ,CAST(ListPrice AS int) AS ListPrice ,[ModifiedDate] ,[Size] FROM [AdventureWorks2012].[Production].[Product] WHERE ListPrice>10 AND Size is not null |
Convert Size column(data type is nvarchar) to int data type:
It will receive the following error due to values that are not compatible with the int data type in the “Size” column.
|
1 2 3 4 5 6 7 8 |
SELECT TOP (5) [Name] ,[ListPrice] ,[ModifiedDate] ,CONVERT(int, Size) AS Size ,CAST(Size AS int) AS Size FROM [AdventureWorks2012].[Production].[Product] WHERE ListPrice>10 AND Size is not null |
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the nvarchar value ‘S’ to data type int.
But when you go to the Results tab, you will see that the data types that are compatible with the int data type are returned as a result.
Convert the ModifiedDate column(data type is datetime) to the varchar data type:
|
1 2 3 4 5 6 7 8 9 |
SELECT TOP (5) [Name] ,[ListPrice] ,[ModifiedDate] ,CONVERT(varchar(50), ModifiedDate) AS [ModifiedDate] ,CAST(ModifiedDate AS varchar(50)) AS [ModifiedDate] ,[Size] FROM [AdventureWorks2012].[Production].[Product] WHERE ListPrice>10 AND Size is not null |
The difference of the CONVERT function is obvious here. With the CONVERT function, we can change the format that we want to show the date as follows.
|
1 2 3 4 5 6 7 8 9 |
SELECT TOP (5) [Name] ,[ListPrice] ,[ModifiedDate] ,CONVERT(varchar(50), ModifiedDate,101) AS [ModifiedDate] ,CAST(ModifiedDate AS varchar(50)) AS [ModifiedDate] ,[Size] FROM [AdventureWorks2012].[Production].[Product] WHERE ListPrice>10 AND Size is not null |
Below you can see the formats you can choose.
|
100 |
mon dd yyyy hh:miAM/PM |
|
101 |
mm/dd/yyyy |
|
102 |
yyyy.mm.dd |
|
103 |
dd/mm/yyyy |
|
104 |
dd.mm.yyyy |
|
105 |
dd-mm-yyyy |
|
106 |
dd mon yyyy |
|
107 |
Mon dd, yyyy |
|
108 |
hh:mm:ss |
|
109 |
mon dd yyyy hh:mi:ss:mmmAM/PM |
|
110 |
mm-dd-yyyy |
|
111 |
yyyy/mm/dd |
|
112 |
yyyymmdd |
|
113 |
dd mon yyyy hh:mi:ss:mmm |
|
114 |
hh:mi:ss:mmm |
|
120 |
yyyy-mm-dd hh:mi:ss |
|
121 |
yyyy-mm-dd hh:mi:ss.mmm |
|
126 |
yyyy-mm-ddThh:mi:ss.mmm |
|
127 |
yyyy-mm-ddThh:mi:ss.mmm with timezone |
|
130 |
dd mon yyyy hh:mi:ss:mmmAM/PM |
|
131 |
dd/mm/yy hh:mi:ss:mmmAM/PM |
