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: plus2net
SELECT CONVERT('plus2net', CHAR(5)); -- Output: plus2
SELECT CONVERT('plus2net', CHAR(10)); -- Output: plus2net

SELECT CONVERT('plus2net', CHAR ASCII); -- Output: plus2net
SELECT CONVERT('plus2net', CHAR UNICODE); -- Output: 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: 2017-07-28
SELECT CONVERT('2017-08-25',DATE); -- Output: 2017-08-25
SELECT CONVERT('2017-02-29',DATE); -- Output: 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: 23:50:12
SELECT CONVERT('-838:50:50',TIME); -- Output: -838:50:50
SELECT CONVERT('860:55:00',TIME); -- Output: 838:59:59
SELECT CONVERT('23:61:45',TIME); -- Output: NULL
SELECT CONVERT('23:41:60',TIME); -- Output: NULL 
SELECT CONVERT('28:47:55',TIME); -- Output: 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: 2017-07-28 23:55:57
SELECT CONVERT('2017-07-28 23:60:57',DATETIME); -- Output: NULL
SELECT CONVERT('2017-07-28 23:57:60',DATETIME); -- Output: NULL
SELECT CONVERT('2017-07-28 24:60:59',DATETIME); -- Output: NULL
SELECT CONVERT('23:41:50',DATETIME); -- Output: NULL 
SELECT CONVERT('2017-07-28',DATETIME); -- Output: 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: 26
SELECT CONVERT('69.345',DECIMAL); -- Output: 69
SELECT CONVERT(25.69873,DECIMAL(4,2)); -- Output: 25.70
SELECT CONVERT(25.69873,DECIMAL(5,2)); -- Output: 25.70
SELECT CONVERT(258.69873,DECIMAL(4,2)); -- Output: 99.99
SELECT CONVERT(258.69873,DECIMAL(5,2)); -- Output: 258.70
SELECT CONVERT(258.69873,DECIMAL(4,1)); -- Output: 258.7

CONVERT to SIGNED

SELECT CONVERT(234, SIGNED); -- Output: 234
SELECT CONVERT('abc', SIGNED); -- Output: 0 with a conversion warning
SELECT CONVERT('234', SIGNED); -- Output: 234

CONVERT to UNSIGNED

SELECT CONVERT(234, UNSIGNED); -- Output: 234
SELECT CONVERT('abc', UNSIGNED); -- Output: 0 with a conversion warning
SELECT CONVERT('234', UNSIGNED); -- Output: 234
SELECT CONVERT(12-15, UNSIGNED); -- Negative value wraps to the UNSIGNED range
SELECT CONVERT(15-12, UNSIGNED); -- Output: 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
substring_index to get part of string using delimiter

All String Functions String reference

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: