MySQL MONTHNAME() from numbers

Question

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?

score 82 · Accepted Answer · answered Aug 11 '11 at 13:57

82

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.

answered Aug 11 '11 at 13:57

Michael Berkowski

267,341
46
444
390

thanks for the warning. The query I am doing only goes back a year or two, so max 24 rows. – Hunter McMillen Aug 11 '11 at 14:01
Perhaps could store month names in MEMORY table and INNER JOIN it. or apply that in application... – Dor Aug 11 '11 at 14:14
5

this only works if sql_mode is not NO_ZERO_DATE, otherwise it will return NULL – Charon ME Jun 04 '19 at 13:59

score 8 · Answer 2 · answered Aug 11 '11 at 13:59

8

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

answered Aug 11 '11 at 13:59

Mchl

61,444
9
118
120

Nicola Cossu · Answer 3 · 2011-08-11T14:07:12.837

2

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

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

but his one is much better. :)

edited Aug 11 '11 at 14:07

answered Aug 11 '11 at 14:01

Nicola Cossu

54,599
15
92
98

score 0 · Answer 4 · answered Nov 29 '16 at 09:21

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

score 0 · Answer 5 · answered Oct 16 '18 at 13:53

0

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

answered Oct 16 '18 at 13:53

Ather Hashmi

111
3
14

score 0 · Answer 6 · answered Jun 19 '23 at 04:36

    SELECT 
        `mktp_target`.`id` AS `id`, 
        `mktp_target`.`target_year` AS `target_year`,
        `mktp_target`.`target_month` AS `target_month`,
        monthname(concat(`mktp_target`.`target_year`,'-',lpad(`mktp_target`.`target_month`, 2, '0'),'-', '01')) AS `target_month_name`
    FROM
        `mktp_target`

MySQL MONTHNAME() from numbers

6 Answers6

Linked

Related