CONVERT() function to convert one data type to other in MYSQL

This function takes input of any type and convert to of specified type.
CONVERT function is same as CAST function

CONVERT to CHAR

SELECT CONVERT('plus2net', CHAR);// Output is plus2net
SELECT CONVERT('plus2net', CHAR(5)); // Output is plus2
SELECT CONVERT('plus2net', CHAR(10)); // Output is plus2net

SELECT CONVERT('plus2net', CHAR ASCII);// Output is plus2net
SELECT CONVERT('plus2net', CHAR UNICODE); // Output is plus2net

CONVERT to DATE

Date format should be in YYYY-MM-DD. Valid date to be used as input. Range for date is '1000-01-01' to '9999-12-31'

SELECT CONVERT('2017-07-28',DATE) // Output is 2017-07-28
SELECT CONVERT('2017-08-25',DATE); // Output is 2017-08-25
SELECT CONVERT('2017-02-29',DATE); // Output is NULL

CONVERT to TIME

Time format should be in HH:MM:SS. Or it can be HHH:MM:SS. Range of time is '-838:59:59' to '838:59:59' as input. If we enter time value beyond this range then output will be limited to this range only.

SELECT CONVERT('23:50:12',TIME); // Output is 23:50:12
SELECT CONVERT('-838:50:50',TIME); // Output is -838:50:50
SELECT CONVERT('860:55:00',TIME);  // Output is 838:59:59
SELECT CONVERT('23:61:45',TIME);   // Output is NULL
SELECT CONVERT('23:41:60',TIME);   // Output is NULL 
SELECT CONVERT('28:47:55',TIME);   // Output is 28:47:55

CONVERT to DATETIME

Date Time format should be in YYYY-MM-DD HH:MM:SS.

SELECT CONVERT('2017-07-28 23:55:57',DATETIME); // Output is 2017-07-28 23:55:57
SELECT CONVERT('2017-07-28 23:60:57',DATETIME); // Output is NULL
SELECT CONVERT('2017-07-28 23:57:60',DATETIME); // Output is NULL
SELECT CONVERT('2017-07-28 24:60:59',DATETIME);   // Output is NULL
SELECT CONVERT('23:41:50',DATETIME);   // Output is NULL 
SELECT CONVERT('2017-07-28',DATETIME);   // Output is 2017-07-28 00:00:00

CONVERT to DECIMAL

Decimal with optional values of total digits including number of decimal places. Here DECIMAL ( 5,2 ) mean total digits ( fixed + decimal ) is 5 and decimal places to the right is 2.

SELECT CONVERT(25.698,DECIMAL); // Output is 26
SELECT CONVERT('69.345',DECIMAL); // Output is 69
SELECT CONVERT(25.69873,DECIMAL(4,2)); // Output is 25.70
SELECT CONVERT(25.69873,DECIMAL(5,2));  //Output is 25.70
SELECT CONVERT(258.69873,DECIMAL(4,2)); //Output is 99.99
SELECT CONVERT(258.69873,DECIMAL(5,2)); //Output is 258.70
SELECT CONVERT(258.69873,DECIMAL(4,1)); //Output is 258.7

CONVERT to SIGNED

SELECT CONVERT(234,SIGNED); // Output is 234
SELECT CONVERT('abc',SIGNED);// Output is 0
SELECT CONVERT('234',SIGNED); // Output is 234

CONVERT to UNSIGNED

SELECT CONVERT(234,UNSIGNED); // Output is 234
SELECT CONVERT('abc',UNSIGNED);// Output is 0
SELECT CONVERT('234',UNSIGNED); // Output is 234
SELECT CONVERT(12-15,UNSIGNED);  //Output is 18446744073709551613
SELECT CONVERT(15-12,UNSIGNED);  //Output is 3

Storing integer part only

In a table we have one column which stores strings like this, Q1,Q2,Q3 …. Q31. Write a query to store the integer part in another integer column.
CAST is used to convert string to UNSIGNED number while using ORDER BY query
SQL String References substring_index to get part of string using delimiter

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: