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
DateTime 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.