Oracle ADD_MONTHS in Snowflake, Databricks and BigQuery
Snowflake ADD_MONTHS keeps the Oracle month-end rule. Databricks add_months and BigQuery DATE_ADD accept the call but only clamp to month end when the day does not exist, so Feb 28 + 1 month gives Mar 28 instead of Mar 31.
Oracle function: ADD_MONTHS(date, integer) · Oracle docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | ADD_MONTHS(date_or_timestamp_expr, num_months_expr) | If the input is the last day of a month, the result is the last day of the result month, as in Oracle. | Snowflake docs |
| Databricks | Same name | add_months(startDate, numMonths) | Returns a DATE. Clamps to the end of the month only when the day does not exist. | Databricks docs |
| BigQuery | Rewrite | DATE_ADD(date, INTERVAL n MONTH) | Clamps to the end of the month only when the day does not exist. | BigQuery docs |
Behavior differences to check
Snowflake
- Snowflake DATEADD(month, ...) does not apply the last-day rule; only ADD_MONTHS does. Keep ADD_MONTHS when the Oracle behavior matters.
Databricks
- Oracle ADD_MONTHS(DATE '2022-02-28', 1) returns 2022-03-31 because Feb 28 is the last day of February. Databricks and BigQuery return 2022-03-28. Month-end schedules (billing, accruals) are where this shows up.
BigQuery
- Oracle ADD_MONTHS(DATE '2022-02-28', 1) returns 2022-03-31 because Feb 28 is the last day of February. Databricks and BigQuery return 2022-03-28. Month-end schedules (billing, accruals) are where this shows up.
Examples
Contract end date 12 months after start
SELECT contract_id, ADD_MONTHS(start_date, 12) AS end_date FROM contracts;SELECT contract_id, ADD_MONTHS(start_date, 12) AS end_date FROM contracts;SELECT contract_id, add_months(start_date, 12) AS end_date FROM contracts;SELECT contract_id, DATE_ADD(start_date, INTERVAL 12 MONTH) AS end_date FROM contracts;