Find last day of a month in Hive

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.

Solution 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.

Solution 2:

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

Solution 3:

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))

Solution 4:

Something like the below could give you some inspiration. The code will give you the last day of the previous month. You can adopt it to get the last day of any month you want.

date_sub(concat(from_unixtime(unix_timestamp(), 'yyyy-MM'), '-01'), 1)