Find last day of a month in Hive

Question

My question is : Is there a way to do find the last day of a month in Hive, like Oracle SQL function ? :

LAST_DAY(D_Dernier_Jour)

Thanks.

Answer 1

As of Hive 1.1.0, last_day(string date) function is available.

last_day(string date)

Returns the last day of the month which the date belongs to. date is a string in the format 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'. The time part of date is ignored.

Answer 2

If you want to avoid custom UDF below is another solution: to_date(date_sub(add_months(concat(from_unixtime(unix_timestamp('2015-07-28','yyyy-MM-dd'), 'yyyy-MM'),'-01'),1),1))

Answer 3

You could make use of last_day(dateString) UDF provided by Nexr. It returns the last day of the month based on a date string with yyyy-MM-dd HH:mm:ss pattern.

Example:
SELECT last_day('2003-03-15 01:22:33') FROM src LIMIT 1;
2003-03-31 00:00:00

You need to pull it from their Github Repository and build. Their wiki page contains all the info on how to build and use it with Hive.

HTH