Introduction
In this article, we will see how to use the cast and convert functions in SQL Server. The cast and convert functions provide similar functionality. They are used to convert a value from one data type to another. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
Cast() Function in SQL Server
The Cast() function is used to convert a data type variable or data from one data type to another data type. The Cast() function provides a data type to a dynamic parameter (?) or a NULL value.
Syntax
CAST ( [Expression]
The data type to which you are casting an expression is the target type. The data type of the expression from which you are casting is the source type.
Example 1
DECLARE @A varchar(2)
DECLARE @B varchar(2)
DECLARE @C varchar(2)
set @A=25
set @B=15
set @C=33
Select CAST(@A as int) + CAST(@B as int) +CAST (@C as int) as Result
![Cast() Function in SQL Server]()
Example 2
DECLARE @Z char(30)
SELECT @Z=current_timestamp
select CAST (@Z as date) as result
![Cast() Function in SQL Server]()
Convert() Function in SQL Server
When you convert expressions from one type to another, in many cases there will be a need within a stored procedure or another routine to convert data from a DateTime type to a varchar type. The Convert function is used for such things. The CONVERT() function can be used to display date/time data in various formats.
Syntax
CONVERT(data_type(length), expression, style)
Style- style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).
Example 1
In this example we take a style value 108 which defines the following format:
hh:mm:ss
Now use the above style in the following query:
select convert(varchar(20),GETDATE(),108)
![Convert() Function in SQL Server]()
In this example we use the style value 107 which defines the following format:
Mon dd, yy
Now use that style in the following query:
select convert(varchar(20),GETDATE(),107)
![Convert() Function in SQL Server]()
Example 2
In this example, we see different style values which define the following format.
SELECT CONVERT(VARCHAR(15),GETDATE(),6)
go
SELECT CONVERT(VARCHAR(16),GETDATE(),106)
go
SELECT CONVERT(VARCHAR(24),GETDATE(),113)
![Convert() Function in SQL Server]()
select convert(varchar(20),GETDATE(),108)
![Convert() Function in SQL Server]()
Example 3
In this example we use the style value 107 which defines the following format:
Mon dd, yy
Now use that style in the following query:
select convert(varchar(20),GETDATE(),107)
![Convert() Function in SQL Server]()
Example 4
In this example, we see different style values which define the following format.
SELECT CONVERT(VARCHAR(15),GETDATE(),6)
go
SELECT CONVERT(VARCHAR(16),GETDATE(),106)
go
SELECT CONVERT(VARCHAR(24),GETDATE(),113)
![Convert() Function in SQL Server]()
Conclusion
This article taught us Cast() and Convert() Functions with its different types of code examples in SQL Server.