Bert Swope
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;
