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.
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;
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,
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) );
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,
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.
Please Login to comment...