It’s sometimes important to display the month name for the specified date when creating summary reports, pivot tables, and charts. We commonly save data in the database in one of two formats: date with timestamp or plain date. Without having to develop User Defined Functions, we can get the part of the date as well as the month name using built-in functions. Let’s look at how Apache Hive and Apache Impala can help.
Apache Hive:
SELECT DATE_FORMAT(CURRENT_TIMESTAMP(),’MMMMM’);
The query above returns the month of November.
You can use your date column instead of “CURRENT TIMESTAMP.”
Apache Impala:
Without having to think about workarounds, Apache Impala provides a large number of built-in functions. One of these is the DAYNAME function, which shows the day-name for the given date.
SELECT MONTHNAME(NOW());
Returned: November
Please note that this function is available in the latest versions of Apache Impala. If you are using older versions then you should depend on the work-around only as shown below:
SELECT DECODE(MONTH(NOW()),
1, “January”,
2, “February”,
3, “March”,
4, “April”,
5, “May”,
6, “June”,
7, “July”,
8, “August”,
9, “September”,
10, “October”,
11, “November”,
12, “December”) as month_name;
Hope you find this article helpful.
Please subscribe for more interesting updates.
Big Data and SQL 
2 comments