SQL Server Data type conversion

In the previous post, we learnt about data types in T-SQL. In this post we will learn about data type conversion in T-SQL.

Casting is used to convert one data type value into another. The type conversion must be compatible otherwise it will fail. There are two functions available in T-SQL to convert data. 

1. Cast function 
2. Convert function

In T-SQL, the CAST function is used to explicitly convert an expression of one data type into another. The basic syntax of the CAST function is as follows:

CAST (expression AS Datatype [ (length) ])

The breakup of the syntax is as follows.

• Expression: The value or expression that you want to convert.
• Datatype: The target data type to which you want to convert the expression.
• Length: (Optional) for some data types, the length can be specified.

Examples of CAST function:

Casting Numeric to String type:

DECLARE @num INT = 123;
DECLARE @strNum VARCHAR(10);
SET @strNum = CAST(@num AS VARCHAR(10));
SELECT @strNum AS Result;

The above example casts an INT value into a VARCHAR data type.

Casting Date to String type:

DECLARE @date DATETIME = GETDATE();
DECLARE @strDate VARCHAR(20);
SET @strDate = CAST(@date AS VARCHAR(20));
SELECT @strDate AS Result;

In this example, a DATETIME value is cast into a VARCHAR data type to display it in a specific format.

Casting to Numeric type:

DECLARE @decimalNum DECIMAL(10, 2) = 123.45;
DECLARE @intNum INT;
SET @intNum = CAST(@decimalNum AS INT);
SELECT @intNum AS Result;

Here, a DECIMAL value is cast into INT data type.

Casting into CHAR type:

DECLARE @str VARCHAR(10) = 'Hello';
DECLARE @strAsChar CHAR(10);
SET @strAsChar = CAST(@str AS CHAR(10));
SELECT @strAsChar AS Result;

In this example, a VARCHAR value is cast to a fixed-length CHAR data type.

CONVERT function provides functionality similar to CAST function but it is more sophisticated as it allows for more flexibility, especially when it comes to formatting date and time values. Convert function allows you to return formatted data.

The basic syntax of the CONVERT function is as follows:

CONVERT(data_type [ (length) ], expression [, style])

The breakup of the syntax is as follows.

• Datatype: The target data type to which you want to convert the expression.
• Length: (Optional) for some data types, the length can be specified.
• Expression: The value or expression that you want to convert.
• Style: (Optional) for date and time conversions, the style parameter can be used to specify the format. This parameter is specific to date and time data types.

Examples of CONVERT function:

Casting Numeric to String type:

DECLARE @num INT = 123;
DECLARE @strNum VARCHAR(10);
SET @strNum = CONVERT (VARCHAR(10), @num);
SELECT @strNum AS Result;

The above example casts an INT value into a VARCHAR data type.

Casting Date to String type:

DECLARE @date DATETIME = GETDATE();
DECLARE @strDate VARCHAR(20);
SET @strDate = CONVERT (VARCHAR(20), @date, 120);
SELECT @strDate AS Result;

In this example, a DATETIME value is cast into a VARCHAR data type with a specific format (style 120).

Casting to Numeric type:

DECLARE @decimalNum DECIMAL (10, 2) = 123.45;
DECLARE @intNum INT;
SET @intNum = CONVERT (INT, @decimalNum);
SELECT @intNum AS Result;

Here, a DECIMAL value is cast into INT data type.

Casting into CHAR type:

DECLARE @str VARCHAR(10) = 'Hello';
DECLARE @strAsChar CHAR (10);
SET @strAsChar = CONVERT (CHAR (10), @str);
SELECT @strAsChar AS Result;

In this example, a VARCHAR value is cast to a fixed-length CHAR data type.

Difference between Cast and Convert in T-SQL

1. CAST function takes single parameter while CONVERT function takes three parameters. The third parameter is optional in convert function.

• CAST (expression AS data_type [ (length) ])
• CONVERT(data_type [ (length) ], expression [, style])

2. CAST function works in both ANSI-SQL and T-SQL but CONVERT function works in T-SQL only.
3. The formatting in case of CAST is in-build and rigid. CONVERT function has style parameter which is used for formatting data in flexible way.

We have already learnt about how to CREATE Database. In the next post we will learn about how to ALTER Database.