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

ðŸŽ¥ Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and projectâ€‘oriented with real examples and source code.

Subscribe to our YouTube Channel here

CONVERT data from one datatype to other