MySQL Tutorial MySQL Advanced MySQL Database Account Management MySQL References

MySQL CAST() Function



The MySQL CAST() function converts a value from one datatype to another datatype.

Syntax

CAST(value AS type)

Parameters

value Required. Specify the value to convert.
type Required. Specify the datatype to convert to. It can be one of the following:

ValueDescription
DATEConverts value to DATE type, which has a date portion only. Format is 'YYYY-MM-DD'.
Supported range is '1000-01-01' to '9999-12-31'.
DATETIMEConverts value to DATETIME type, which has both date and time portions. Format is 'YYYY-MM-DD HH:MM:SS'.
Supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
TIMEConverts value to TIME type, which has a time portion only. Format is 'HH:MM:SS'.
Supported range is '-838:59:59' to '838:59:59'.
DECIMALConverts value to DECIMAL type. Optional parameters M and D can be used to specify the maximum number of digits (M) and the number of digits following the decimal point (D).
CHARConverts value to CHAR type, which is a fixed length string.
NCHARConverts value to NCHAR type. It is like CHAR, but produces a string with the national character set.
SIGNEDConverts value to SIGNED type, which is a signed 64-bit integer.
UNSIGNEDConverts value to UNSIGNED type, which is an unsigned 64-bit integer.
BINARYConverts value to BINARY type, which is a binary string.

Return Value

Returns the converted value.

Convert to DATE

The CAST() function can be used to convert a value to a DATE type. For example - in the example below, '2018-08-18' is converted to DATE datatype.

mysql> SELECT CAST('2018-08-18' AS DATE);
Result: '2018-08-31'

Convert to DATETIME

The CAST() function can be used to convert a value to a DATETIME type. For example - in the example below, '2018-08-18 10:38:42' is converted to DATETIME datatype.

mysql> SELECT CAST('2018-08-18 10:38:42' AS DATETIME);
Result: '2018-08-18 10:38:42'

Convert to TIME

The CAST() function can be used to convert a value to a TIME type. For example - in the example below, '10:38:42' is converted to TIME datatype.

mysql> SELECT CAST('10:38:42' AS TIME);
Result: '10:38:42'

Convert to DECIMAL

The CAST() function can be used to convert a value to a DECIMAL type. For example - in the example below, '123.456' is converted to DECIMAL datatype.

mysql> SELECT CAST('123.456' AS DECIMAL(5, 2));
Result: 123.46

Convert to CHAR

The CAST() function can be used to convert a value to a CHAR type. For example - in the example below, 123 is converted to CHAR datatype.

mysql> SELECT CAST(123 AS CHAR);
Result: '123'

Convert to NCHAR

The CAST() function can be used to convert a value to a NCHAR type. For example - in the example below, 123 is converted to NCHAR datatype.

mysql> SELECT CAST(123 AS NCHAR);
Result: '123'

Convert to SIGNED

The CAST() function can be used to convert a value to a SIGNED type. For example - in the example below, 10-20 is converted to SIGNED datatype.

mysql> SELECT CAST(10-20 AS SIGNED);
Result: -10

Convert to UNSIGNED

The CAST() function can be used to convert a value to a UNSIGNED type. For example - in the example below, 10-20 is converted to UNSIGNED datatype.

mysql> SELECT CAST(10-20 AS UNSIGNED);
Result: 18446744073709551606

Convert to BINARY

The CAST() function can be used to convert a value to a BINARY type. For example - in the example below, 123 is converted to BINARY datatype.

mysql> SELECT CAST(123 AS BINARY);
Result: '123'

❮ MySQL Functions