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

nah just change your data type to varchar so that you can store something like that but this will slows down your searching, perhaps I suggest you to seperate them in 2 columns year and month that you can filter them in future for searching — Beginner, Feb 21 '19 at 08:40

score 0 · Accepted Answer · answered Feb 21 '19 at 09:02

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');

Mysql month number to month name conversion

1 Answers1