Oracle MONTHS_BETWEEN in Snowflake, Databricks and BigQuery
Snowflake and Databricks keep MONTHS_BETWEEN with Oracle-style fractions based on 31-day months. BigQuery has no equivalent; DATE_DIFF(..., MONTH) returns whole month boundaries only.
Oracle function: MONTHS_BETWEEN(date1, date2) · Oracle docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | MONTHS_BETWEEN(date_expr1, date_expr2) | Fraction based on 31-day months; the same day of month gives a whole number. | Snowflake docs |
| Databricks | Same name | months_between(expr1, expr2 [, roundOff]) | Fraction based on 31 days per month, rounded to 8 digits unless roundOff is false. | Databricks docs |
| BigQuery | Rewrite | DATE_DIFF(date1, date2, MONTH) | Counts month boundaries and returns an integer, not a fraction. | BigQuery docs |
Behavior differences to check
Databricks
- Results are rounded to 8 decimal places by default, so equality checks against Oracle values may fail in the last digits.
BigQuery
- Age or tenure calculations such as FLOOR(MONTHS_BETWEEN(SYSDATE, hire_date) / 12) need a different formula; DATE_DIFF(CURRENT_DATE(), hire_date, YEAR) counts year boundaries, not completed years.
Examples
Tenure in months
SELECT emp_id, MONTHS_BETWEEN(SYSDATE, hire_date) AS tenure_months FROM employees;SELECT emp_id, MONTHS_BETWEEN(CURRENT_DATE, hire_date) AS tenure_months FROM employees;SELECT emp_id, months_between(current_date(), hire_date) AS tenure_months FROM employees;SELECT emp_id, DATE_DIFF(CURRENT_DATE(), hire_date, MONTH) AS tenure_months FROM employees;