How to calculate months between in hive?

Question

Is there a way to calculate months between two dates based on month and year

for eg 2016-01-01 2017-01-22

I need 12 to be returned in integer format in hive.

Solution

months_between UDF is available since Hive 1.2.0

https://issues.apache.org/jira/browse/HIVE-9518

The manual is here: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

months_between takes care about days, not only year and month:

hive> select abs(cast(months_between('2016-01-10', '2017-01-10')as int));
OK
12
Time taken: 1.812 seconds, Fetched: 1 row(s)

hive> select abs(cast(months_between('2016-01-10', '2017-01-01')as int));
OK
11
Time taken: 0.084 seconds, Fetched: 1 row(s)

If you want it to calculate exactly based on month and year, use trunc() function:

hive> select abs(cast(months_between(trunc('2016-01-10','MM'), trunc('2017-01-01','MM'))as int));
OK
12
Time taken: 0.123 seconds, Fetched: 1 row(s)

Comments

[user2672739]

I know that but it wont calculate exactly based on month and year which should calculate only months like 12 months not to consider day

[lalit panwar]

This will not consider the year factor Suppose date1 = 2020-09-01 and date2 = 2021-11-01 your answer will be 2 instead of 14