MySQL: CONVERT Function

This tutorial explains how to use the MySQL CONVERT function with syntax and examples.

Description

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

Syntax

There are 2 syntaxes for the CONVERT function - one syntax to convert datatypes and one syntax to convert character sets.

The first syntax for the CONVERT function is used to convert one datatype to another datatype in MySQL:

CONVERT( value, type )

OR

The second syntax for the CONVERT function is used to convert one character set to another character set:

CONVERT( value USING character_set )

Parameters or Arguments

value

The value to convert.

type

The datatype that you wish to convert value to. type 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'.
CHAR	Converts value to CHAR type, which is a fixed length string.
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.

character_set

The character set to convert to.

Note

• Starting in MySQL 4.0.6, you can use CHAR as type in the CONVERT function.
• See also CAST function.

Applies To

The CONVERT function can be used in the following versions of MySQL:

• MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0.2

Example

Let's look at some MySQL CONVERT function examples and explore how to use the CONVERT function in MySQL.

Convert to Date

The first syntax for the MySQL CONVERT function allows you to convert a value from one datatype to another datatype. Let's look at how to use the CONVERT function to convert a value to a DATE type. For example:

mysql> SELECT CONVERT('2014-02-28', DATE);
Output: '2014-02-28'

This CONVERT example takes the value '2014-02-28' and converts it to a DATE datatype.

Convert to DATETIME

This CONVERT function example shows how to convert a value to a DATETIME type. For example:

mysql> SELECT CONVERT('2014-02-28 08:14:57', DATETIME);
Output: '2014-02-28 08:14:57'

This CONVERT example takes the value '2014-02-28 08:14:57' and converts it to a DATETIME datatype.

Convert to TIME

This CONVERT function example shows how to convert a value to a TIME type. For example:

mysql> SELECT CONVERT('08:14:57', TIME);
Output: '08:14:57'

This CONVERT example takes the value '08:14:57' and converts it to a TIME datatype.

Convert to CHAR

This CONVERT function example shows how to convert a value to a CHAR type. For example:

mysql> SELECT CONVERT(125, CHAR);
Output: '125'

This CONVERT example takes the value 125 and converts it as a CHAR datatype with the value of '125'.

Convert to SIGNED

This CONVERT function example shows how to convert a value to a SIGNED type. For example:

mysql> SELECT CONVERT(4-6, SIGNED);
Output: -2

This CONVERT example takes the value 4-5 and converts it as a SIGNED datatype with the value of -2.

Convert to UNSIGNED

This CONVERT function example shows how to convert a value to an UNSIGNED type. For example:

mysql> SELECT CONVERT(4-6, UNSIGNED);
Output: 18446744073709551614

This CONVERT example takes the value 4-5 and converts it as an UNSIGNED datatype with the value of 18446744073709551614.

Convert to BINARY

This CONVERT function example shows how to convert a value to a BINARY type. For example:

mysql> SELECT CONVERT('4', BINARY);
Output: '4'

This CONVERT example takes the value '4' and converts it as a BINARY datatype with the binary string value of '4'.

Convert Character Sets

The second syntax for the MySQL CONVERT function allows you to convert a value from one character set to another. Let's look at how to use the CONVERT function to convert between character sets. For example:

mysql> SELECT CONVERT('AODBA.com' USING utf8);
Output: 'AODBA.com'

This CONVERT example takes the value 'AODBA.com' and converts it from the current character set to the utf8 character set.

We could change our example above to convert the value 'AODBA.com' to the latin1 character set as follows:

mysql> SELECT CONVERT('AODBA.com' USING latin1);
Output: 'AODBA.com'