MySQL CONVERT() Function

  1. Overview
  2. MYSQL
  3. Advanced Functions
  4. MySQL CONVERT() Function

The CONVERT() function in MySQL is used to convert a value into a specific datatype or character set. It allows conversion of data types such as dates, decimals, and even string encodings.

Syntax

CONVERT(value, type)

OR:

CONVERT(value USING charset)

Parameters

  1. value: The value that you want to convert.

  2. type: The target datatype to which you want to convert the value. Some possible types are:

    • DATE: Converts the value to the DATE datatype in the format YYYY-MM-DD.
    • DATETIME: Converts the value to the DATETIME datatype in the format YYYY-MM-DD HH:MM:SS.
    • DECIMAL: Converts the value to the DECIMAL type, which can also include optional parameters for maximum digits (M) and digits after the decimal (D).
    • TIME: Converts the value to the TIME datatype in the format HH:MM:SS.
    • CHAR: Converts the value to CHAR (a fixed-length string).
    • NCHAR: Similar to CHAR, but it uses a national character set.
    • SIGNED: Converts the value to a signed 64-bit integer.
    • UNSIGNED: Converts the value to an unsigned 64-bit integer.
    • BINARY: Converts the value to a binary string.

  3. charset (optional): If you are converting the value to a specific character set, use this parameter. For example, CONVERT(value USING utf8).

Definition and Usage

  • The CONVERT() function allows you to cast or convert a value from one datatype to another, such as from a string to a date or from an integer to a decimal.
  • It can also be used to change the character encoding of a string.

Technical Details

  • Works in: From MySQL 4.0
  • Return Type: The type to which you are converting the value.
  • Return Values: It returns the value after converting it into the specified datatype or character set.

Examples

Example 1: Convert a String to a Date

Convert a string representing a date into the DATE datatype:

SELECT CONVERT("2017-08-29", DATE);

This will return 2017-08-29 as a DATE datatype.

Example 2: Convert a String to DATETIME

Convert a string representing a datetime into the DATETIME datatype:

SELECT CONVERT("2017-08-29 12:45:00", DATETIME);

This will return 2017-08-29 12:45:00 as a DATETIME datatype.

Example 3: Convert a Value to DECIMAL

Convert a string representing a number to a DECIMAL type:

SELECT CONVERT("12.34", DECIMAL(5,2));

This will return 12.34 as a DECIMAL value.

Example 4: Convert a String to a Different Character Set

Convert a string into a specific character set (e.g., utf8):

SELECT CONVERT('Hello' USING utf8);

This will return the string 'Hello' converted to the utf8 character set.

Use Cases

  • Data Type Conversion: Useful when you need to convert between various data types such as DATE, DATETIME, DECIMAL, etc.
  • Character Set Conversion: It can be used to convert strings between different encodings like utf8, latin1, etc.
  • Database Compatibility: This function can be helpful when moving data between databases that use different character sets or data types.
Was this article helpful?