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
|age_in_months(Now(), '1988-06-21'), —calculates completed period without current month|
|age_in_years(Now(), '1988-06-21'), —calculates completed period without current month|
|datediff('month', '1988-06-21', Now()) as MonthsBetweenDates, —includes current month in calculation|
|datediff('year', '1988-06-21', Now()) as YearsBetweenDates, —includes current month in calculation|
|months_between (Now(),'2017-01-01') — calculates months between two dates as a float|
|period_diff(201712, 201707) as Past, —months between now and a past date|
|period_diff(201806, 201712) as Future —months between now and a future date|