Open In App

MySQL | CAST( ) Function

Improve
Improve
Like Article
Like
Save
Share
Report

The MySQL CAST() function is used for converting a value from one datatype to another specific datatype. The CAST() function accepts two parameters which are the value to be converted and the datatype to which the value needs to be converted. 

The datatypes in which a given value can be converted are:

  1. DATE: It is used to convert a value to the DATE datatype. The Format returned is “YYYY-MM-DD”.
  2. DATETIME: It is used to convert a value to the DATETIME datatype. The Format returned is “YYYY-MM-DD HH:MM: SS”.
  3. TIME: It is used to convert a value to the TIME datatype. The Format returned is “HH:MM: SS”.
  4. CHAR: It is used to convert a value to the CHAR datatype.
  5. SIGNED: It is used to convert a value to a SIGNED datatype.
  6. UNSIGNED: It is used to convert a value to an UNSIGNED datatype.
  7. BINARY: It is used to convert a value to a BINARY datatype.

Syntax:

CAST(input_value AS datatype)

Parameters Used:

  • input_value – It is used to specify the value which needs to be converted.
  • datatype – It is used to specify the datatype in which the value needs to be converted.

Return Value: The MySQL CAST() function returns a value in the desired datatype after conversion. Supported Versions of MySQL:

  • MySQL 5.7
  • MySQL 5.6
  • MySQL 5.5
  • MySQL 5.1
  • MySQL 5.0
  • MySQL 4.1
  • MySQL 4.0
  • MySQL 3.23

Example 1: 

Implementing CAST() function to convert a value to DATE datatype.

SELECT CAST("2023-04-19" AS DATE); 

Output:

cast

 

Example 2: 

Implementing CAST() function to convert a value to CHAR datatype.

SELECT CAST(121 AS CHAR); 

Output:

output3

 

Example 3: 

Implementing CAST() function to convert a value to the SIGNED datatype.

SELECT CAST(2-4 AS SIGNED); 

Output:

 

Example 4: 

Implementing CAST() function to convert a value to the UNSIGNED datatype.

SELECT CAST(2-4 AS UNSIGNED); 

Output:

 

Example

Let’s assume that we have a table and we’ll see how the CAST function interacts with the table in this example. Let’s first create a table called “Orderss” with the following information in it:

Query:

CREATE TABLE Orderss (
   OrderID int NOT NULL,
   CustomerName varchar(255) NOT NULL,
   OrderDate datetime NOT NULL
);

INSERT INTO Orderss (OrderID, CustomerName, OrderDate)
VALUES (1, 'John Doe', CAST('2023-03-15' AS datetime));
INSERT INTO Orderss (OrderID, CustomerName, OrderDate)
VALUES (2, 'Jane Smith', CAST('2022-04-10' AS datetime));

INSERT INTO Orderss (OrderID, CustomerName, OrderDate)
VALUES (3, 'Bob Johnson', CAST('2020e -05-20' AS datetime));

INSERT INTO Orderss (OrderID, CustomerName, OrderDate)
VALUES (4, 'Alice Brown', CAST('2022-06-05' AS datetime));

Output:

 

Now, we will apply some operations to see how the output changes according to the given condition.

Query:

SELECT OrderID, CustomerName, OrderDate
FROM Orderss
WHERE (OrderDate) = 2022
ORDER BY OrderDate DESC;

Output:

IMG

 


Last Updated : 12 May, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads