SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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.Nocurrent datatypeResultant   datatype Behaviour
1NumericNumericIt rounds off the value.
2NumericMoneyIt rounds off the value.
3NumericIntegerIt truncates the value.
4MoneyIntegerIt rounds off the value.
5MoneyNumericIt rounds off the value.
6DatetimeIntegerIt rounds off the value.
7FloatIntegerIt 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 CenturyWith CenturyInput/OutputStandard
1101mm/dd/yyyyUS
2102yyyy.mm.ddANSI
3103dd/mm/yyyyBritish/French
5105dd-mm-yyyyItalian
7107Mon dd, yyyy--
8108hh:mm:ss--
11111yyyy/mm/ddJapan

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 () FunctionCONVERT () 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 -------------- 24Example: 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_NameEmployee_IDEmployee_AgeEmployee_Salary
Rishi301124300000.252
Dhruv301626300000.450
Ayaan301725350000.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_NameEmployee_salary (Integer)
Rishi300000
Dhruv300000
Ayaan350000

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.



ADVERTISEMENT
ADVERTISEMENT