It’s sometimes important to display the day 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 week day name using built-in functions. Let’s look at how Apache Hive and Apache Impala can help.
Apache Hive:
SELECT DATE_FORMAT(DATE_ADD(TO_DATE(CURRENT_TIMESTAMP()), 0), ‘EEEE’);
The current day, i.e. “Friday,” was returned. Instead of 0 you can enter positive and negative values to see Tomorrow’s or Yesterday’s day name,” as shown below.
SELECT DATE_FORMAT(DATE_ADD(TO_DATE(CURRENT_TIMESTAMP()), 1), ‘EEEE’);
This returned tomorrow’s day name.
SELECT DATE_FORMAT(DATE_ADD(TO_DATE(CURRENT_TIMESTAMP()), -1), ‘EEEE’);
This returned yesterday’s day name.
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 DAYNAME(NOW());
Returned: Friday
Hope you find this article helpful.
Please subscribe for more interesting updates.
Big Data and SQL 
2 comments