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:

Value	Description
DATE	Converts 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'.
DATETIME	Converts 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'.
TIME	Converts 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'.
DECIMAL	Converts 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).
CHAR	Converts value to CHAR type, which is a fixed length string.
NCHAR	Converts value to NCHAR type. It is like CHAR, but produces a string with the national character set.
SIGNED	Converts value to SIGNED type, which is a signed 64-bit integer.
UNSIGNED	Converts value to UNSIGNED type, which is an unsigned 64-bit integer.
BINARY	Converts 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