CODEWITHSUNDEEP
mysql
Hunter McMillen
Hunter McMillen

Reputation: 61510

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?

Upvotes: 39

Views: 69584

Answers (6)

Md. Saifur Rahman
Md. Saifur Rahman

Reputation: 404

    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`

Upvotes: 0

Ather Hashmi
Ather Hashmi

Reputation: 121

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

Upvotes: 0

bharat
bharat

Reputation: 1776

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

Upvotes: 0

Nicola Cossu
Nicola Cossu

Reputation: 56367

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

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

but his one is much better. :)

Upvotes: 2

Mchl
Mchl

Reputation: 62395

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

Upvotes: 8

Michael Berkowski
Michael Berkowski

Reputation: 270637

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.

Upvotes: 83

Related Questions