How to get month name from number in mysql

Question

Is there anyway to get the month short name from just the number of the month in mysql ? for example : if I give 3, I need to get Mar.

MONTHNAME(STR_TO_DATE(3, '%m')), I used this but, it is giving month full name. But, I want short name — Banala Ramu, Apr 30 '13 at 11:36
@banalaramu use then **substring()** with **str_to_date()**. — Vucko, Apr 30 '13 at 11:37

score 5 · Answer 1 · answered Apr 30 '13 at 11:36

5

Try this:

SELECT MONTHNAME(STR_TO_DATE(1, '%m'));//returns January

Also refer this

answered Apr 30 '13 at 11:36

Linga

10,379
10
52
104

2

But I want short name like, Jan – Banala Ramu Apr 30 '13 at 11:39

score 2 · Answer 2 · answered Apr 30 '13 at 11:40

2

Use the following functions: Month Number: MONTH() Month Name: MONTHNAME() You should use monthname() function.

answered Apr 30 '13 at 11:40

Max White

21
1

score 1 · Answer 3 · answered Apr 30 '13 at 11:40

1

As per the manual, '%b' does just what you're after

answered Apr 30 '13 at 11:40

Strawberry

33,750
13
40
57

score 0 · Answer 4 · answered Apr 30 '13 at 11:37

There are a few options to retrieve this, date_format being one them, but it depends on how and what exactly you are trying to implement. I suggest you take a look at the MySQL Date documentation:

https://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

score 0 · Answer 5 · answered Apr 30 '13 at 11:40

0

SELECT DATE_FORMAT('2013-12-01' + INTERVAL n MONTH, '%b') As Month_Name

change n to the month number.

answered Apr 30 '13 at 11:40

John Woo

258,903
69
498
492

try this one. just change `n` to your integer value. – John Woo Apr 30 '13 at 11:45

score 0 · Answer 6 · edited Nov 29 '13 at 08:34

0

Hello this should answer your query

SELECT left(MONTHNAME(STR_TO_DATE(1, '%m')),3) from dual

dual is used a spoof table when we dont want to refer a table in mysql but just want to execute the query.

edited Nov 29 '13 at 08:34

user987339

10,519
8
40
45

answered Nov 29 '13 at 08:05

jyotiska

31
6

score 0 · Answer 7 · answered Feb 25 '15 at 13:02

0

SELECT MONTHNAME(CONCAT(your_column)) as month from table_name;

answered Feb 25 '15 at 13:02

Praveen Srinivasan

1,562
4
25
52

hey there. adding a link to documentation would be great! – Félix Adriyel Gagnon-Grenier Feb 28 '15 at 16:18

score 0 · Answer 8 · answered Nov 08 '19 at 21:25

I know this question is aged, but found it relevant today.

Solution:

SELECT DATE_FORMAT(CONCAT(YEAR(NOW()),'-',LPAD( myMonthInteger ,2,'0'),'-01'),'%b')

Change the myMonthInteger value to your column name or month index value (1-12). Returns NULL for other values.

Example using OP value of "3" for myMonthInteger:

SELECT DATE_FORMAT(CONCAT(YEAR(NOW()),'-',LPAD( 3,2,'0'),'-01'),'%b')

returns "Mar"

Explanation:

LPAD( myMonthInteger ,2,'0')

Adds a leading zero to the month integer if needed.

CONCAT(YEAR(NOW()),'-',LPAD( myMonthInteger ,2,'0'),'-01')

Creates a valid mysql date string with the current year and first day of month

DATE_FORMAT(CONCAT(YEAR(NOW()),'-',LPAD( myMonthInteger ,2,'0'),'-01'),'%b')

Formats the date string with '%b' type - short month name

How to get month name from number in mysql

8 Answers8

Linked