SQL Server CAST() and CONVERT() Functions

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 2008 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=50

set @B=15

set @C=35

Select CAST(@A as int) + CAST(@B as int) +CAST (@C as int) as Result

Output: 100

Example :

DECLARE @Z char(30)

SELECT @Z=current_timestamp

select CAST (@Z as date) as result

Output: 2015-03-30

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: 14:59:55

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:

Mar 30, 2015

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:

30 Mar 15

30 Mar 2015

30 Mar 2015 15:02:51:150