SQL Tutorial SQL Advanced SQL Database SQL References

MySQL CONVERT() Function



The MySQL CONVERT() function converts a value from one datatype to another, or one character set to another.

Syntax

/* converts one datatype to another datatype */
CONVERT(value, type)

/* converts one character set to another character set */
CONVERT(value USING character_set)

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.
character_set Required. Specify the character set to convert to.

Return Value

Returns the converted value.

Convert to DATE

The CONVERT() 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 CONVERT('2018-08-18', DATE);
Result: '2018-08-31'

Convert to DATETIME

The CONVERT() 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 CONVERT('2018-08-18 10:38:42', DATETIME);
Result: '2018-08-18 10:38:42'

Convert to TIME

The CONVERT() 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 CONVERT('10:38:42', TIME);
Result: '10:38:42'

Convert to DECIMAL

The CONVERT() 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 CONVERT('123.456', DECIMAL(5, 2));
Result: 123.46

Convert to CHAR

The CONVERT() 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 CONVERT(123, CHAR);
Result: '123'

Convert to NCHAR

The CONVERT() 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 CONVERT(123, NCHAR);
Result: '123'

Convert to SIGNED

The CONVERT() 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 CONVERT(10-20, SIGNED);
Result: -10

Convert to UNSIGNED

The CONVERT() 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 CONVERT(10-20, UNSIGNED);
Result: 18446744073709551606

Convert to BINARY

The CONVERT() 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 CONVERT(123, BINARY);
Result: '123'

Convert Character Sets

The CONVERT() function allows you to convert a value from one character set to another. For example - in the example below, 'alphacodingskills.com' is converted from the current character set to the UTF8 character set.

mysql> SELECT CONVERT('alphacodingskills.com' USING UTF8);
Result: 'alphacodingskills.com'

Similarly, to convert the value 'alphacodingskills.com' to the latin1 character set, the following query can be used:

mysql> SELECT CONVERT('alphacodingskills.com' USING latin1);
Result: 'alphacodingskills.com'

❮ MySQL Functions