Cast() and Convert() Functions in SQL Server

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 

Example 2

DECLARE @Z char(30)  
SELECT @Z=current_timestamp  
select CAST (@Z as date) as result  

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)  

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)  

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)

select convert(varchar(20),GETDATE(),108)  

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)

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)

Conclusion

This article taught us Cast() and Convert() Functions with its different types of code examples in SQL Server.