Output only the name of the month instead of the month number in MySQL

To display only the month number, use DATE_FORMAT() along with STR_TO_DATE(). Let us first create a table:

mysql> create table DemoTable1320
-> (
-> MonthName varchar(20)
-> );
Query OK, 0 rows affected (0.43 sec)

Example

Insert some records in the table using insert command −

mysql> insert into DemoTable1320 values('10/10/2010');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable1320 values('11/12/2018');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable1320 values('12/01/2019');
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable1320;

Output

+------------+
| MonthName  |
+------------+
| 10/10/2010 |
| 11/12/2018 |
| 12/01/2019 |
+------------+
3 rows in set (0.00 sec)

Example

Here is the query to outputting the name of the month instead of the number −

mysql> select date_format(str_to_date(MonthName,'%d/%m/%Y'),'%M') from DemoTable1320;

Output

+-----------------------------------------------------+
| date_format(str_to_date(MonthName,'%d/%m/%Y'),'%M') |
+-----------------------------------------------------+
| October                                             |
| December                                            |
| January                                             |
+-----------------------------------------------------+
3 rows in set (0.00 sec)

AmitDiwan

Updated on: 2020-07-08T08:09:12+05:30

162 Views

Kickstart Your Career

Get certified by completing the course

Get Started