Open In App

MySQL | CAST( ) Function

Last Updated : 12 May, 2023
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

 


Previous Article
Next Article

Similar Reads

MySQL | Common MySQL Queries
MySQL server is a open-source relational database management system which is a major support for web based applications. Databases and related tables are the main component of many websites and applications as the data is stored and exchanged over the web. Even all social networking websites mainly Facebook, Twitter, and Google depends on MySQL dat
9 min read
MySQL | LEAD() and LAG() Function
The LEAD() and LAG() function in MySQL are used to get preceding and succeeding value of any row within its partition. These functions are termed as nonaggregate Window functions. The Window functions are those functions which perform operations for each row of the partition or window. These functions produce the result for each query row unlikely
3 min read
MySQL | BIN() Function
The BIN() function in MySQL is used to convert a decimal number to its binary equivalent.The BIN() function is equivalent to the CONV() function written in the format CONV(number,10,2). In this format CONV() function converts the number 'number' from base 10(decimal) to base 2(binary). It is important to note that the BIN() function can only be use
1 min read
MySQL | LAST_DAY() Function
The LAST_DAY() function in MySQL can be used to know the last day of the month for a given date or a datetime. The LAST_DAY() function takes a date value as argument and returns the last day of month in that date. The date argument represents a valid date or datetime. Syntax: LAST_DAY( Date ); If the date or datetime value is invalid, the function
2 min read
CURRENT_TIME() function in MySQL
CURRENT_TIME() function in MySQL is used to check the current time. It returns the current time as a value in 'hh:mm:ss' or hhmmss format, depending on whether the function is used in string or numeric context. Syntax : CURRENT_TIME OR CURRENT_TIME(precision) Parameter : This method accepts one parameter. precision - It specifies the returned fract
2 min read
LOCATE() function in MySQL
LOCATE() function in MySQL is used for finding the location of a substring in a string. It will return the location of the first occurrence of the substring in the string. If the substring is not present in the string then it will return 0. When searching for the location of a substring in a string it does not perform a case-sensitive search. Synta
2 min read
WEEKOFYEAR() Function in MySQL
WEEKOFYEAR() function in MySQL is used to find the week number for a given date. If the date is NULL, the WEEKOFYEAR function will return NULL. Otherwise, it returns the value of week which ranges between 1 to 53. Note: The WEEKOFYEAR() function considers the first week of the year to be the week that contains the first day of January. Also, the we
3 min read
EXP() Function in MySQL
EXP() function in MySQL is used to returns E raised to the power of a specified number. Here E(2.718281...) is the base of the natural logarithm. Syntax : EXP(X) Parameter : This method accepts one parameter as mentioned above in the syntax and described below : X – A specified number which will be used as a power of E. Returns : It returns E raise
2 min read
BIT_AND() function in MySQL
BIT_AND() : This function in MySQL is used to return the Bitwise AND of all bits in a given expression. It first converts all decimal values into binary values, and then perform bitwise and operation on those binary values. The BIT_AND() function works by performing a bitwise AND operation on each pair of corresponding bits in the binary representa
3 min read
MOD() Function in MySQL
The MOD() function in MySQL is used to find the remainder of one number divided by another. The MOD() function returns the remainder of dividend divided by divisor. if the divisor is zero, it returns NULL. Syntax: MOD(N, M) or N % M or N MOD M Parameter : MOD() function accepts two parameter as mentioned above and described below. N -The dividend i
4 min read
Article Tags :