MySQL CAST()

The CAST() function in SQL language is a transtyping function that allows you to convert a data of one type into another. For example, it is possible to transform a DATETIME format into DATE, or the opposite.
 

Syntax:

The syntax of the CAST() function is as follows:

SELECT CAST( expression AS type );

In this syntax, “expression” refers to the value to be converted, while “type” contains the type of data to be obtained. This data type can be one of the following values:

• DATE
• DATETIME
• TIME
• BINARY
• CHAR

 

 

Example :

Let’s assume that any application has a database to store information. This database contains the table of users with their id, name, email, and the date the user was added. This date (in DATETIME format) is very useful to know exactly the day the user was added.
 
User Table:

+----------+---------+--------+---------------------+----------+
|  userID  |  name   |  age   |  registration_date  |  salary  |
+----------+---------+--------+---------------------+----------+
|    101   |  Alex   |   25   | 2022-09-12 14:21:08 | 2503.33  |
|    102   |  Emily  |   15   | 2022-01-12 02:31:09 | 6325.55  |
|    103   |  Jean   |   35   | 2022-01-22 11:51:33 | 5631.20  |
|    104   |  Bob    |   40   | 2022-02-01 03:22:40 | 3232.89  |
+----------+---------+--------+---------------------+----------+

In this table, the column types are as follows:

• userID: BIGINT
• name: VARCHAR
• age: BIGINT
• registration_date: DATETIME
• salary: FLOAT

 

 

Extract date in DATE format:

It is possible to get registration_date in DATE format to keep only the year, the month and the day. This allows you to ignore the time.

SELECT userID, name, age, CAST(registration_date AS DATE) AS registration_date_cast, salary
FROM user

Output:

+----------+---------+-----+---------------------+------------------------+----------+
|  userID  |  name   | age |  registration_date  | registration_date_cast |  salary  |
+----------+---------+-----+---------------------+------------------------+----------+
|    101   |  Alex   | 25  | 2022-09-12 14:21:08 | 2022-09-12             | 2503.33  |
|    102   |  Emily  | 15  | 2022-01-12 02:31:09 | 2022-01-12             | 6325.55  |
|    103   |  Jean   | 35  | 2022-01-22 11:51:33 | 2022-01-22             | 5631.20  |
|    104   |  Bob    | 40  | 2022-02-01 03:22:40 | 2022-02-01             | 3232.89  |
+----------+---------+-----+---------------------+------------------------+----------+

 

 

Extract the salary in INTEGER format:

Thanks to the CAST() function it is also possible to convert a decimal number into an integer. The “salary” column is initially of type FLOAT but it is possible to use CAST() to convert its type to an INTEGER.

SELECT userID, name, age, registration_date, CAST(salary AS SIGNED INTEGER ) AS salary_cast
FROM user

Output:

+----------+---------+--------+---------------------+----------+
|  userID  |  name   |  age   |  registration_date  |  salary  |
+----------+---------+--------+---------------------+----------+
|    101   |  Alex   |   25   | 2022-09-12 14:21:08 |  2503    |
|    102   |  Emily  |   15   | 2022-01-12 02:31:09 |  6325    |
|    103   |  Jean   |   35   | 2022-01-22 11:51:33 |  5631    |
|    104   |  Bob    |   40   | 2022-02-01 03:22:40 |  3232    |
+----------+---------+--------+---------------------+----------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More