MySQL MONTHNAME() from numbers

Is there anyway to get the MONTHNAME() from just the number of the month (1-12)? For example if I have 6,7,8 is there any native way in MySQL to transform those into June,July,开发者_开发问答August?

You can use STR_TO_DATE() to convert the number to a date, and then back with MONTHNAME()

SELECT MONTHNAME(STR_TO_DATE(6, '%m'));

+---------------------------------+
| MONTHNAME(STR_TO_DATE(6, '%m')) |
+---------------------------------+
| June                            |
+---------------------------------+

Warning: This could be slow if done over a lot of rows.

A somewhat ugly way would be SELECT MONTHNAME(CONCAT('2011-',8,'-01'));

Before reading Michael's great answer I had thought something like this

select elt(3,'January','February','March',....)

but his one is much better. :)

Such way you can get different language weekname or month name

to localize the weekday:

`SELECT ELT( WEEKDAY('2004-04-10')+1, 'Montag','Dienstag','Mittwoch','Donnerstag','Freitag','Samstag','Sonntag');`

long version with month:

`SELECT DATE_FORMAT( '2004-04-10', CONCAT( ELT( WEEKDAY('2004-04-10')+1, 'Montag','Dienstag','Mittwoch','Donnerstag','Freitag','Samstag','Sonntag'),', %d. ', ELT( MONTH('2004-04-10'), 'Januar','Februar','März','April','Mai','Juni','Juli','August','September','Oktober','November','Dezember'),' %Y'));`

--> Samstag, 10. April 2004

same for unix-timestamp:

`SELECT DATE_FORMAT( FROM_UNIXTIME(1081548000), CONCAT( ELT( WEEKDAY(FROM_UNIXTIME(1081548000))+1, 'Mo','Di','Mi','Do','Fr','Sa','So'),', %d. ', ELT( MONTH(FROM_UNIXTIME(1081548000)), 'Jan.','Feb.','März','April','Mai','Juni','Juli','Aug.','Sept.','Okt.','Nov.','Dez.'),' %Y'));`

--> Sa, 10. April 2004

try this: select monthname('2016-01-01') for January or select monthname('2016-03-01') for March