How to calculate months between two dates in Netezza

Technology - How to calculate months between two dates in Netezza

Recently, I had reason to calculate the months between two dates to test some data against business rules/requirements.  I was pleasantly surprised that Netezza had an easy-to-use function ‘months_between’ function, to calculate the difference.

To calculate the number of months between two dates in Netezza, you can use the MONTHS_BETWEEN function. This function takes two date values as input and returns the number of months between them as a decimal value.

The Months Between SQL Function Syntax

The months_Between function uses two dates to perform the calculation.  Whether you want the output to be a positive or negative number determines the field order within the function.

  • For a positive number result, put the Newest Date Field first, separated by a comma, then Oldest Date Field.
  • For a negative number result, just reverse the order putting the Oldest Date Field first, separated by a comma, then the Newest Date Field.

The results will contain a decimal for the days of the month and you will need to round, based on your business requirements, to achieve a whole number.

SELECT months_between(<<DateField>>, <<DateField>>) as <<OutputFiledName>>,

from <<TableName>>;

Example Months Between SQL

SELECT months_between(current_date, Date(‘2017-01-01’)) as Difference_In_Months,

round(months_between(current_date, Date(‘2017-01-01’)) ) as Difference_In_Months_Rounded

from _v_dual;

Netezza, PureData, Months Between Two Dates, function, SQL, difference between two dates
Netezza PureData Months Between Two Dates

Related References

Date/time functions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Date/time functions

%d bloggers like this: