When working with a number different of data types the MySQL CAST()
function comes in handy. It will help in casting the specified expressions to a specified data type. Typecasting refers to the process of manually changing the data type of an expression.
The CONVERT()
function is equivalent of the CAST()
Function. To understand this further think of the CAST()
Function as converting a NUMERIC
datatype to CHAR
datatype. The conversion only lasts during the query, which means the datatype change is temporary.
Also read: MySQL STR_TO_DATE() – Convert Date String to MySQL Date
Syntax of MySQL CAST()
![CAST Syntax 1](https://mysqlcode.com/wp-content/uploads/2021/12/CAST-Syntax-1.png)
CAST(EXPRESSION AS DATATYPE)
Code language: SQL (Structured Query Language) (sql)
You can cast expressions to many different data types in MySQL, let’s look at some of them:
Working with MySQL CAST()
Let’s now try casting different types of values into the CAST() method and converting them.
1. CAST to BINARY
![BINARY EXAMPLE](https://mysqlcode.com/wp-content/uploads/2021/12/BINARY-EXAMPLE.png)
SELECT CAST("MySQLCode" AS BINARY);
In this example, we took a string and converted it to BINARY
. Here, the type of output is in VARBINARY
which is similar to VARCHAR
.
2. CAST to CHAR
![CHAR EXAMPLE](https://mysqlcode.com/wp-content/uploads/2021/12/CHAR-EXAMPLE.png)
SELECT CAST("MySQLCode" AS CHAR);
Casting as a CHAR
will give us the output as a VARCHAR
. The output in the above example is a VARCHAR
.
![NCHAR EXAMPLE](https://mysqlcode.com/wp-content/uploads/2021/12/NCHAR-EXAMPLE.png)
SELECT CAST("MySQLCode" AS NCHAR);
Code language: SQL (Structured Query Language) (sql)
The NCHAR
type is the same as CHAR
with one difference which is it uses the National Character Set. Other than that NCHAR
is similar to CHAR
.
3. CAST to DATE
![DATE EXAMPLE](https://mysqlcode.com/wp-content/uploads/2021/12/DATE-EXAMPLE.png)
SELECT CAST("11-11-11" AS DATE);
Code language: SQL (Structured Query Language) (sql)
The DATE the
type has the format of 'YYYY-MM-DD'
. In this the year is specified first then the month and then the day.
4. CAST to DATETIME
![DATETIME EXAMPLE](https://mysqlcode.com/wp-content/uploads/2021/12/DATETIME-EXAMPLE.png)
SELECT CAST("11-11-11" AS DATETIME);
Code language: SQL (Structured Query Language) (sql)
DATETIME
is similar to DATE
but it also includes time. If the time isn’t specified like in the above example it will us 00:00:00 by default. The format that DATETIME
uses is 'YYYY-MM-DD hh:mm:ss'
.
5. CAST to TIME
Format for TIME
is "hh:mm:ss"
![TIME As A String](https://mysqlcode.com/wp-content/uploads/2021/12/TIME-as-a-String.png)
SELECT CAST("09:33:33" AS TIME);
Code language: SQL (Structured Query Language) (sql)
In the above example, we have used a string that is converted to TIME
.
![TIME As A Number](https://mysqlcode.com/wp-content/uploads/2021/12/TIME-as-a-Number.png)
SELECT CAST(093333 AS TIME);
Code language: SQL (Structured Query Language) (sql)
In the above example, we have used a number that is converted to TIME
.
6. CAST to YEAR
Before looking at an example, let us understand the rules that we have to follow when casting to this type.
If we cast a four-digit number in the range of 1901 to 2155 where 2155 both are included, even a string in this range will directly be cast to the YEAR
.
If a number or a string which consists of one or two digits will be cast to YEAR
as follows.
When the number ranges from 1 to 69 it will be in the 2000s.
![Min Value Of 2000s](https://mysqlcode.com/wp-content/uploads/2021/12/min-value-of-2000s.png)
SELECT CAST(1 AS YEAR);
Code language: SQL (Structured Query Language) (sql)
![Max Value Of 2000s](https://mysqlcode.com/wp-content/uploads/2021/12/max-value-of-2000s.png)
SELECT CAST(69 AS YEAR);
Code language: SQL (Structured Query Language) (sql)
When the number ranges from 70 to 99 it will be in the 1900s.
![Min Value Of 1900s](https://mysqlcode.com/wp-content/uploads/2021/12/min-value-of-1900s.png)
SELECT CAST(70 AS YEAR);
Code language: SQL (Structured Query Language) (sql)
![Max Value Of 1900s](https://mysqlcode.com/wp-content/uploads/2021/12/max-value-of-1900s.png)
SELECT CAST(99 AS YEAR);
Code language: SQL (Structured Query Language) (sql)
When a string is “0”, the returned value will be 2000.
![0 As A String Using The Type YEAR](https://mysqlcode.com/wp-content/uploads/2021/12/0-as-a-string-using-the-type-YEAR.png)
SELECT CAST("0" AS YEAR);
Code language: SQL (Structured Query Language) (sql)
When the number is 0, the returned value will be 0.
![0 As A Number Using The Type YEAR](https://mysqlcode.com/wp-content/uploads/2021/12/0-as-a-number-using-the-type-YEAR.png)
SELECT CAST(0 AS YEAR);
Code language: SQL (Structured Query Language) (sql)
If the number is out of any of the ranges mentioned above will result in the output is NULL
.
7. Other types
DECIMAL
DOUBLE
FLOAT
JSON
REAL
SIGNED
- spatial_type
UNSIGNED
Conclusion
The CAST()
function is super useful and easy to implement. One thing to note is that not anything can be cast to any data type. There are rules that are to be followed in the background when converting from one data type to another. To receive the result one expects they should consider this before doing any cast operation.
You can read more about the CAST()
function here.
You can read more about VARBINARY
here.
You can read more about National Character Set here.