hive get each month end date

Question

I want each month last date ,like Jan - 31 , Feb - 28 , so on. I tried below with current_date and it works but when I use my date column it returns null:

SELECT datediff(CONCAT(y, '-', (m + 1), '-', '01'), CONCAT(y, '-', m, '-', '01')) FROM 
(SELECT
month(from_unixtime(unix_timestamp(C_date, 'yyyyMMdd'),'yyyy-MM-dd') ) as m, 
year(from_unixtime(unix_timestamp(C_date, 'yyyyMMdd'),'yyyy-MM-dd') ) as y, 
day(from_unixtime(unix_timestamp(C_date, 'yyyyMMdd'),'yyyy-MM-dd') )
from table2 ) t

returns :

_c0
    NULL 

SELECT
month(from_unixtime(unix_timestamp(C_date, 'yyyyMMdd'),'yyyy-MM-dd') ) as m, 
year(from_unixtime(unix_timestamp(C_date, 'yyyyMMdd'),'yyyy-MM-dd') ) as y, 
day(from_unixtime(unix_timestamp(C_date, 'yyyyMMdd'),'yyyy-MM-dd') )
from table2) t

returns:

m   | y  | _c2|
3   |2017|  21|

Thanks in advance.

Possible duplicate of [Find last day of a month in Hive](https://stackoverflow.com/questions/17548584/find-last-day-of-a-month-in-hive) — Kaushik Nayak, Dec 15 '17 at 16:29
select last_day(current_date) - returns full date 2017-12-31 , i need only 31 so unless i substr i would not get 31 — DrSD, Dec 15 '17 at 16:32
There is calendar utilities in hive, so try with hive UDF functions — Kanagaraj Dhanapal, Dec 15 '17 at 18:16

score 0 · Accepted Answer · answered Dec 21 '17 at 12:54

0

Use last_day() and day() functions:

hive> select day(last_day(current_date)) ;
OK
31
Time taken: 0.079 seconds, Fetched: 1 row(s)

Apply to_date() to convert your column before applying last_day().

answered Dec 21 '17 at 12:54

leftjoin

36,950
8
57
116

hive get each month end date

1 Answers1