Cast() and Convert() Functions in SQL Server

In this article, we will see how to use the cast and convert functions in SQL Server 2012. 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

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]
AS Datatype)

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

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

OUTPUT

Example

DECLARE @Z char(30)

SELECT @Z=current_timestamp

select CAST (@Z as date) as result

OUTPUT

Convert() Function

When you convert expressions from one type to another, in many cases there will be a need within a stored procedure or other 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

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)

OUTPUT

Example

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)

OUTPUT

Example

In this example we see different style values which defines the following format.

SELECT CONVERT(VARCHAR(15),GETDATE(),6)

go

SELECT CONVERT(VARCHAR(16),GETDATE(),106)

go

SELECT CONVERT(VARCHAR(24),GETDATE(),113)

OUTPUT