MySQL CAST() Function

The CAST() function in MySQL is used to convert a value from one datatype to another. It allows you to explicitly convert between various data types, such as from CHAR to DATE or from VARCHAR to DECIMAL, depending on your needs.

Syntax

CAST(value AS datatype)

Parameters

Parameter	Description
`value`	Required. The value to convert.
`datatype`	Required. The datatype to convert the value to.

Datatypes Available

Value	Description
`DATE`	Converts the value to `DATE`. Format: `"YYYY-MM-DD"`
`DATETIME`	Converts the value to `DATETIME`. Format: `"YYYY-MM-DD HH:MM:SS"`
`DECIMAL`	Converts the value to `DECIMAL`. You can specify precision with optional `M` and `D` parameters (e.g., `DECIMAL(10,2)` for 10 digits with 2 decimal places).
`TIME`	Converts the value to `TIME`. Format: `"HH:MM:SS"`
`CHAR`	Converts the value to `CHAR` (a fixed-length string).
`NCHAR`	Converts the value to `NCHAR` (similar to `CHAR`, but produces a string with the national character set).
`SIGNED`	Converts the value to `SIGNED` (a signed 64-bit integer).
`UNSIGNED`	Converts the value to `UNSIGNED` (an unsigned 64-bit integer).
`BINARY`	Converts the value to `BINARY` (a binary string).

Definition and Usage

The CAST() function is used to convert a value of one datatype to another, such as converting a string to a date or a number to a string.
It can be especially useful when performing operations that require specific data types, such as date comparisons or numeric calculations.

Example Usage

Example 1: Convert a String to a `DATE`

SELECT CAST("2017-08-29" AS DATE);

This converts the string "2017-08-29" to a DATE type.

Example 2: Convert a String to a `DECIMAL`

SELECT CAST("123.45" AS DECIMAL(5,2));

This converts the string "123.45" to a DECIMAL with a precision of 5 digits and 2 decimal places.

Example 3: Convert a String to a `SIGNED` Integer

SELECT CAST("100" AS SIGNED);

This converts the string "100" to a signed integer.

Technical Details

Works in: From MySQL 4.0.
Return Type: Returns the value converted to the specified datatype.

Tips

If the value cannot be converted to the specified datatype, MySQL will return NULL.
You can also use the CONVERT() function as an alternative to CAST(), with a similar syntax and functionality.