CAST and CONVERT Functions in SQL Server(TSQL)

 

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)

Convert ListPrice column(data type is money) to varchar and int data type:

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.

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:

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.

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

 

 

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *