Calculating the Last Day of the current month

If you’re familiar with the Hive syntax you may be aware of the last_day function. In Arcadia, there’s no built-in function for that calculation. However, you can use the trunc() function to achieve a similar outcome. The example below is the result a query that shows both the current time and also the last day of the current month:

> select now() as 'today', (trunc(now(), 'month') + interval 1 month - interval 1 day) as 'last_day_of_month';

+-------------------------------+---------------------+
| today                         | last_day_of_month   |
+-------------------------------+---------------------+
| 2019-04-22 13:43:52.688616000 | 2019-04-30 00:00:00 |
+-------------------------------+---------------------+
1 Like

Another creative way to use the Last Day calculation is to show only the partitions from the last day of the last 6 months dynamically.

select * from mydb.sales
where to_date(order_date) in ((trunc(now(), 'month') - interval 1 day), 
(trunc(now(), 'month') - interval 1 month - interval 1 day), 
(trunc(now(), 'month') - interval 2 month - interval 1 day), 
(trunc(now(), 'month') - interval 3 month - interval 1 day), 
(trunc(now(), 'month') - interval 4 month - interval 1 day), 
(trunc(now(), 'month') - interval 5 month - interval 1 day))

Below is an illustration of what each of those date intervals look like when calculated:

34%20PM