CODEWITHSUNDEEP
mysql
Banala Ramu
Banala Ramu

Reputation: 304

How to get month name from number in mysql

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.

Upvotes: 0

Views: 19277

Answers (8)

bluemccarty
bluemccarty

Reputation: 3

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

Upvotes: 0

Praveen Srinivasan
Praveen Srinivasan

Reputation: 1620

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

Upvotes: 0

jyotiska
jyotiska

Reputation: 41

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.

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

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

Upvotes: 1

Max White
Max White

Reputation: 21

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

Upvotes: 2

John Woo
John Woo

Reputation: 263763

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

change n to the month number.

Upvotes: 0

Borniet
Borniet

Reputation: 3546

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

Upvotes: 0

Linga
Linga

Reputation: 10563

Try this:

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

Also refer this

Upvotes: 5

Related Questions