<< SQL Server Hosting - HostForLIFE :: How To Split A String In SQL? | SQL Server Hosting - HostForLIFE :: SQL Server INFORMATION_SCHEMA Views >>

SQL Server Hosting - HostForLIFE :: Cast() and Convert() Functions in SQL Server

December 13, 2021 06:07 by author

Peter

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 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]
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 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 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 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
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 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 defines 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 defines the following format.
    SELECT CONVERT(VARCHAR(15),GETDATE(),6)
    go
    SELECT CONVERT(VARCHAR(16),GETDATE(),106)
    go
    SELECT CONVERT(VARCHAR(24),GETDATE(),113)