Recently, I had reason to during the months between two dates to test some data against business rule/requirement. Pleasantly surprised I was to find that Netezza had an easy to use function ‘months_between’ function to calculate the difference.
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;
Related References
- Netezza / PureData – How to convert a timestamp to date in SQL
- PureData / Netezza – What date ranges are supported by Netezza?
- Netezza / PureData – How to Number for day of week in SQL?
- Netezza / PureData – SQL Cast Conversion to Integers
- Netezza / PureData – Substring Function
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
