MySQL CONVERT() Function: Change Data Types and Character Sets
Master data type and character set conversions with the MySQL `CONVERT()` function. This guide explains how `CONVERT()` provides greater flexibility than `CAST()`, offering detailed examples of how to change data types and character sets within your MySQL queries for effective data manipulation.
MySQL CONVERT() Function
The CONVERT() function in MySQL changes a value's data type or character set.
CONVERT() Function: Definition and Usage
This function is incredibly useful for data manipulation and ensuring your data is in the correct format. It's similar to the CAST() function, but offers more options, especially regarding character sets.
Syntax
There are two main ways to use CONVERT():
Syntax
-- For changing data types
CONVERT(value, type)
-- For changing character sets
CONVERT(value USING charset)
Parameter Values
| Parameter | Description |
|---|---|
value |
The value you want to convert. This is required. |
type |
The data type you want to convert to. This is required when changing data types. See the list of options below. |
charset |
The character set you want to convert to. This is required when changing character sets. |
Data Types for CONVERT()
| Value | Description | Format Example |
|---|---|---|
DATE |
Converts to DATE | "YYYY-MM-DD" |
DATETIME |
Converts to DATETIME | "YYYY-MM-DD HH:MM:SS" |
DECIMAL(M,D) |
Converts to DECIMAL. M is the maximum number of digits, D is the number of digits after the decimal point. |
e.g., `DECIMAL(10,2)` |
TIME |
Converts to TIME | "HH:MM:SS" |
CHAR |
Converts to CHAR (fixed-length string) | N/A |
NCHAR |
Converts to NCHAR (national character set) | N/A |
SIGNED |
Converts to SIGNED (64-bit integer) | N/A |
UNSIGNED |
Converts to UNSIGNED (64-bit integer) | N/A |
BINARY |
Converts to BINARY (binary string) | N/A |
Examples
Convert to DATE
Syntax
SELECT CONVERT("2017-08-29", DATE);
Output
2017-08-29
Convert to CHAR
Syntax
SELECT CONVERT(150, CHAR);
Output
150
Convert to TIME
Syntax
SELECT CONVERT("14:06:10", TIME);
Output
14:06:10
Convert Character Set
Syntax
SELECT CONVERT("W3Schools.com" USING latin1);
Output
W3Schools.com (The output might appear the same, but the underlying character set changes)