Mysql month number to month name conversion

Question

I have month value like "22018" in my column I need it like Feb-2018 in mysql workbench

Answer 1

You need to first extract the month from the date (considering it will have one or two digits), e.g.:

SELECT LPAD(SUBSTRING('22018', 1, LENGTH('22018') - 4), 2, '0');

This will give you 02. Now, you can extract the year with similar logic, e.g.:

SELECT SUBSTRING('22018', LENGTH('22018') - 4 + 1, LENGTH('22018'));

Finally, you can concatenate all these to get a string like 2018-02-01:

SELECT CONCAT(SUBSTRING('22018', LENGTH('22018') - 4 + 1, LENGTH('22018')),
'-',
LPAD(SUBSTRING('22018', 1, LENGTH('22018') - 4), 2, '0'), '-01');

Once this is done, you can use DATE_FORMAT function to get the required output:

SELECT DATE_FORMAT(CONCAT(SUBSTRING('22018', LENGTH('22018') - 4 + 1, 
LENGTH('22018')),
'-',
LPAD(SUBSTRING('22018', 1, LENGTH('22018') - 4), 2, '0'), '-01'), '%M-%Y');