Hive query to return last date of each month for 3 years

Question

Please provide hive query to return last date of each month in 'yyyy-mm-dd' format for 3 years.

Answer 1

Substitute start and end dates in this example with yours. How it works: space function generates string of spaces with length = number of days returned by datediff() function, split by space creates an array, posexplode explodes an array, returning position of the element in the array, which corresponds to the number of days. Then date_add('${hivevar:start_date}',s.i) returns dates for each day, lest_day() function (exists in Hive since 1.1version) converts each date to the last day (need distinct here). Run this example:

set hivevar:start_date=2015-07-01;
set hivevar:end_date=current_date;

select distinct last_day(date_add ('${hivevar:start_date}',s.i)) as last_date 
  from ( select posexplode(split(space(datediff(${hivevar:end_date},'${hivevar:start_date}')),' ')) as (i,x)
        ) s
order by last_date
;

Output:

OK
2015-07-31
2015-08-31
2015-09-30
2015-10-31
2015-11-30
2015-12-31
2016-01-31
2016-02-29
2016-03-31
2016-04-30
2016-05-31
2016-06-30
2016-07-31
2016-08-31
2016-09-30
2016-10-31
2016-11-30
2016-12-31
2017-01-31
2017-02-28
2017-03-31
2017-04-30
2017-05-31
2017-06-30
2017-07-31
2017-08-31
2017-09-30
2017-10-31
2017-11-30
2017-12-31
2018-01-31
2018-02-28
2018-03-31
2018-04-30
2018-05-31
2018-06-30
2018-07-31
Time taken: 71.581 seconds, Fetched: 37 row(s)