SQL Query to Convert Month Number to Month Name

SQL stands for structure query language, which used in the database to retrieve data, update and modify data in relational databases like MySql, Oracle, etc. And a query is a question or request for data from the database, that is if we ask someone any question then the question is the query. Similarly when we want any data from database then we write the query in SQL to get that data. In this article, we are talking about how we can convert month number to month name using SQL.

Creating database

To create a database there is a query we need to use in the SQL platform, like MySql, Oracle, etc. The query is,

create database database_name;

For example,

create database sales;

using the database

To use the database there is a query we need to use in the SQL portal, like MySql, Oracle, etc. The  query is,

use database_name;

Add tables in the database

To create tables in a database there is a query we need to use in the SQL platform, like MySql, Oracle, etc. The query  is,

create table table_name(
column1 type(size),
column2 type(size),
.
.
.
columnN type(size)
);

For example,

create table sales_detail(
sales_ID INT,
sales_price INT,
sales_product VARCHAR(30),
sales_date DATE
);

See the table

To see the table use the ‘DESC table_name’ query, here the query is,

desc sales_detail;

Add value into the table

To add value to the table there is a query we need to use in the SQL platform, like MySql, Oracle, etc. The query is,

insert into table_name(
value1,
value2,
value3
.
.
.

valueN);

For example, here the query will be,

INSERT INTO `sales_detail` (`sales_ID`, `sales_price`, `sales_product`, `sales_date`)
 VALUES
(1, '3500', 'SQL', '2021-03-02'),
(2, '4500', 'JAVA', '2020-11-09'),
(3, '5000', 'DSA', '2021-01-08'),
(4, '4000', 'Python', '2021-02-06');

Data present in the table after insertion

select * from sales_detail;

Now we have to convert the month number to month name for the above table. There are several ways to do that, see below,

1. To convert month number to month name we have to use a function MONTHNAME(), this function takes date column or a date as a string and returns the Month name corresponding to the month number.

 SELECT sales_product, MONTHNAME(sales_date) from sales_detail;

Here this function takes the “sales_date” column as an argument and returns the month name.

2.  Using date_format() function, we can convert the month number to the month name. This function takes the month and a format specifier as argument and returns the value specified by the format specifier.  There are some format specifier used in this function , that are , ‘%M’, ‘%Y’, ‘%D’, ‘%H’ etc. Here we use ‘%M’ to convert the month number to month name.

The query for this,

select date_format(sales_date, '%M')as Monthname from sales_detail;

Here the function takes the ‘sales_date‘ column and specifier ‘%M‘ as argument and returns the month name as output in the monthname alias column.

This is how we can convert month number to month name in SQL.