SQL Cast Operator

While writing SQL queries, we can see many values which are to be converted into another datatype for accessing them. In SQL this conversion is generally done by CAST function.

CAST Function:

This function is used for converting an expression from one datatype to another datatype.

Generally, we use cast function to convert expressions from numeric to string datatype or string to numeric datatype.

Syntax for CAST Function:

CAST (expression/value AS resultant_datatype)

Where,

• Expression/value - This is expression which is to be converted to another datatype.
• AS - converting operator.
• resultant_datatype – The datatype to which the expression should be converted.

Example:

select
 CAST (‘2022-06-28’ AS DATETIME) 
result;

This Statement gives the output in date and time format.

OUTPUT:

Result
2022-06-28 00:00:00.000

NOTE: Datatype conversion should be done between two compatible datatypes only.

When we try to convert a string “sarfn” into numeric datatype, we will be encountered with an error. So, cast function is only used between compatible datatypes.

Conversion of Decimal Number to integer datatype:

If we try to convert a Decimal number to an integer, the resultant will be the rounded value of the Decimal number.

Example:

select cast (7.44 as int) result;

OUTPUT:

Result
7

Conversion of String to a Datetime:

If we try to convert a String having a date value to date and time format, the resultant will be a standard format for date and time.

Example:

select cast (‘2022-06-28’ as datetime) result;

OUTPUT:

Result
2022-06-28 00:00:00.000

Datatype conversion Table

Serial.No	current datatype	Resultant   datatype	Behaviour
1	Numeric	Numeric	It rounds off the value.
2	Numeric	Money	It rounds off the value.
3	Numeric	Integer	It truncates the value.
4	Money	Integer	It rounds off the value.
5	Money	Numeric	It rounds off the value.
6	Datetime	Integer	It rounds off the value.
7	Float	Integer	It truncates the value.

TRY_CAST Function:

TRY_CAST function also works similar to CAST() function. This function returns a NULL value when the conversion fails. But in the case of CAST() function an error is raised.

Syntax:

TRY_CAST (expression/value AS datatype[(length)])

• Expression/value - This is expression which is to be converted to another datatype.
• AS - converting operator.
• resultant_datatype – The datatype to which the expression should be converted.

 Example:

Select try_cast(“18 Main” AS float) result;

OUTPUT:

Result
NULL

For the above query if we use CAST() function in place of TRY_CAST() function, we will be raised with an error. This is the main difference between cast () and try_cast() function.

SQL CONVERT () Function:

This function also works same as cast function. Convert () function is specific to only SQL server, whereas CAST Function follows ANSI Standard.

Syntax:

CONVERT (final data type (Length), expression/value, style)

Where,

• final data type: This is the data type to which the expression or value should be converted, after using CONVERT () function.
• Length of the final data type: It is not mandatory to mention the length of final data type.
• Expression/value: Expression or value should be converted to final data type. Expression can be of any data type like integer, varchar, binary.
• Style: This field is also optional in the syntax of convert function. This part is for conversion between date or string format.

Without Century	With Century	Input/Output	Standard
1	101	mm/dd/yyyy	US
2	102	yyyy.mm.dd	ANSI
3	103	dd/mm/yyyy	British/French
5	105	dd-mm-yyyy	Italian
7	107	Mon dd, yyyy	--
8	108	hh:mm:ss	--
11	111	yyyy/mm/dd	Japan

Example:

select convert (integer, 89.98);

 OUTPUT:

Number of Records: 1
89
It shows the truncated value i.e 89.

select convert (varchar, ‘2022-06-29’, 102);

OUTPUT:

Number of Records: 1
2022-06-29

Differences between CAST () Function and CONVERT () Function:

CAST () Function	CONVERT () Function
1. Cast function converts the expression or value from one data type to another data type.	Convert function converts the expression or value from one data type to another data type.
2. Cast function follows ANSI standard.	Convert function is only specific to SQL.
Syntax: CAST (expression/value AS resultant_datatype)	Syntax: CONVERT (final data type (Length), expression/value, style)
Example: select cast (24.31 AS integer) result;   OUTPUT: result -------------- 24	Example: select convert (integer, 24.31);   OUTPUT: Number of records: 1 24

Now let’s look the CAST () Function using Tables.

Consider an Employee Table.

Employee_Name	Employee_ID	Employee_Age	Employee_Salary
Rishi	3011	24	300000.252
Dhruv	3016	26	300000.450
Ayaan	3017	25	350000.000

Consider a situation where the Salaries of all employees should be sum up to know the total amount given in a month for sales report purpose.

So, a rounded off amount is enough for sales report (including decimal points is not mandatory).

For this purpose, we can convert the Employee_Salary into integer data type which is a float data type initially.

The query for the above conversion is,

select Employee_name, cast (Employee_Salary AS Integer) Employee_salary (Integer) from Employee;

OUTPUT:

Employee_Name	Employee_salary (Integer)
Rishi	300000
Dhruv	300000
Ayaan	350000

This is how we use CAST () Function in real-life examples.

In databases related to university, bank databases, and Account databases we can use CAST () Function.