The MySQL CAST() Function

MySQL CAST

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
CAST() Function Syntax
CAST(EXPRESSION AS DATATYPE)

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
BINARY EXAMPLE
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
CHAR EXAMPLE
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
NCHAR EXAMPLE
SELECT CAST("MySQLCode" AS NCHAR);

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
DATE EXAMPLE
SELECT CAST("11-11-11" AS DATE);

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
DATETIME EXAMPLE
SELECT CAST("11-11-11" AS DATETIME);

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
TIME As A String
SELECT CAST("09:33:33" AS TIME);

In the above example, we have used a string that is converted to TIME.

TIME As A Number
TIME As A Number
SELECT CAST(093333 AS TIME);

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
Min Value Of the 2000s
SELECT CAST(1 AS YEAR);
Max Value Of 2000s
Max Value Of the 2000s
SELECT CAST(69 AS YEAR);

When the number ranges from 70 to 99 it will be in the 1900s.

Min Value Of 1900s
Min Value Of the 1900s
SELECT CAST(70 AS YEAR);
Max Value Of 1900s
Max Value Of the 1900s
SELECT CAST(99 AS YEAR);

When a string is “0”, the returned value will be 2000.

0 As A String Using The Type YEAR
“0” As A String Using The Type YEAR
SELECT CAST("0" AS YEAR);

When the number is 0, the returned value will be 0.

0 As A Number Using The Type YEAR
0 As A Number Using The Type YEAR
SELECT CAST(0 AS YEAR);

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.