Apart from the standard datediff() and timestampdiff(), Vertica and MySQL offer a couple of more ways to go about it:
- By using the age functions: age_in_years (current date, start date) and age_in_months (current date, start date)
A key thing to remember about these two is that, unlike datediff (‘years’, start date, current date), they will calculate the months/years between the two dates that have already fully lapsed.
- By using the months_between (current date, start date) function: as this function does not employ a rounding approach, it will return the exact time in decimals between your two dates. This makes it particularly handy for forecasting future inventory levels.
- period_diff (later period, earlier period) calculates the number of months between two year-month periods