SQL Server DATEDIFF in Snowflake, Databricks and BigQuery
The name survives in all three targets, but the results do not always match. SQL Server and Snowflake count unit boundaries crossed. Databricks counts whole elapsed units. BigQuery DATE_DIFF puts the end date first.
SQL Server function: DATEDIFF(datepart, startdate, enddate) · SQL Server docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | DATEDIFF(date_or_time_part, expr1, expr2) | Same argument order and the same boundary counting as SQL Server: DATEDIFF(year, '2024-12-31', '2025-01-01') returns 1. | Snowflake docs |
| Databricks | Same name | datediff(unit, start, end) | Synonym of timestampdiff (Databricks Runtime 10.4 LTS and above). Counts whole elapsed units: one month has passed only when the day and time reach the start value. The two-argument datediff(endDate, startDate) form returns days and takes the end date first. | Databricks docs |
| BigQuery | Rewrite | DATE_DIFF(end_date, start_date, granularity) | End date first. Counts unit boundaries, like SQL Server. Use DATETIME_DIFF or TIMESTAMP_DIFF for other types. | BigQuery docs |
Behavior differences to check
Databricks
- Databricks documents that datediff(MONTH, TIMESTAMP '2021-02-28 12:00:00', TIMESTAMP '2021-03-28 11:59:59') returns 0 because a full month has not elapsed, while SQL Server returns 1 because a month boundary was crossed. Month, quarter and year differences are the most common source of mismatched reports after migration.
- datediff(a, b) with two arguments is end minus start in days, the reverse of the three-argument form.
BigQuery
- Swapping the arguments changes the sign. DATE_DIFF(start, end, DAY) silently returns a negative number.
Examples
Days between order and shipment
SELECT order_id, DATEDIFF(day, ordered_at, shipped_at) AS days_to_ship FROM orders;SELECT order_id, DATEDIFF(day, ordered_at, shipped_at) AS days_to_ship FROM orders;SELECT order_id, datediff(DAY, ordered_at, shipped_at) AS days_to_ship FROM orders;SELECT order_id, DATE_DIFF(DATE(shipped_at), DATE(ordered_at), DAY) AS days_to_ship FROM orders;