SQL Server DATEADD in Snowflake, Databricks and BigQuery
Snowflake and Databricks both have DATEADD(unit, n, date) with the same argument order as SQL Server. BigQuery uses DATE_ADD, DATETIME_ADD or TIMESTAMP_ADD with an INTERVAL, and the date comes first.
SQL Server function: DATEADD(datepart, number, date) · SQL Server docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | DATEADD(date_or_time_part, value, date_or_time_expr) | Same argument order. Also accepts abbreviations such as dd and mm. | Snowflake docs |
| Databricks | Same name | dateadd(unit, value, expr) | Synonym of timestampadd, available in Databricks Runtime 10.4 LTS and above. Returns a TIMESTAMP. The two-argument dateadd(startDate, numDays) form adds days to a DATE. | Databricks docs |
| BigQuery | Rewrite | DATE_ADD(date_expression, INTERVAL int64 date_part) | Pick the function by input type: DATE_ADD for DATE, DATETIME_ADD for DATETIME, TIMESTAMP_ADD for TIMESTAMP. | BigQuery docs |
Behavior differences to check
- Month arithmetic at month end: SQL Server returns the last day of the target month when the day does not exist (Jan 31 + 1 month = Feb 28 or 29). Snowflake DATEADD, Databricks and BigQuery behave the same way for this case.
Databricks
- The three-argument dateadd returns a TIMESTAMP even for DATE input; cast back with CAST(... AS DATE) if a date column is expected.
BigQuery
- Units are keywords, not strings: INTERVAL 7 DAY, not INTERVAL '7' 'day'. Abbreviations such as dd are not accepted.
Examples
Due date 30 days after the invoice date
SELECT invoice_id, DATEADD(day, 30, invoice_date) AS due_date FROM invoices;SELECT invoice_id, DATEADD(day, 30, invoice_date) AS due_date FROM invoices;SELECT invoice_id, CAST(dateadd(DAY, 30, invoice_date) AS DATE) AS due_date FROM invoices;SELECT invoice_id, DATE_ADD(invoice_date, INTERVAL 30 DAY) AS due_date FROM invoices;