SQL Server DATEPART in Snowflake, Databricks and BigQuery
Snowflake has DATE_PART, Databricks has date_part, and BigQuery uses EXTRACT(part FROM date). Weekday and week numbers are where results differ, because SQL Server depends on SET DATEFIRST and language settings.
SQL Server function: DATEPART(datepart, date) · SQL Server docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Different name | DATE_PART(date_or_time_part, expr) | EXTRACT(part FROM expr) is equivalent. Returns NUMBER. | Snowflake docs |
| Databricks | Different name | date_part(fieldStr, expr) | Synonym of extract(field FROM expr). The field is a string such as 'YEAR' or 'DOW'. | Databricks docs |
| BigQuery | Rewrite | EXTRACT(part FROM date_expression) | Parts are keywords: YEAR, QUARTER, MONTH, WEEK, DAY, DAYOFWEEK, DAYOFYEAR. | BigQuery docs |
Behavior differences to check
- DATEPART(weekday, d) follows SET DATEFIRST (Sunday = 1 with the US English default). Databricks DOW and BigQuery DAYOFWEEK also return Sunday = 1; Snowflake depends on the WEEK_START session parameter. Check any code that compares weekday numbers.
- DATEPART(week, d) counts weeks starting January 1 and follows DATEFIRST; ISO weeks are DATEPART(iso_week, d). Map each one to the matching ISO or non-ISO week part of the target.
Examples
Orders per year and month
SELECT DATEPART(year, order_date) AS y, DATEPART(month, order_date) AS m, COUNT(*) FROM orders GROUP BY DATEPART(year, order_date), DATEPART(month, order_date);SELECT DATE_PART(year, order_date) AS y, DATE_PART(month, order_date) AS m, COUNT(*) FROM orders GROUP BY 1, 2;SELECT date_part('YEAR', order_date) AS y, date_part('MONTH', order_date) AS m, COUNT(*) FROM orders GROUP BY 1, 2;SELECT EXTRACT(YEAR FROM order_date) AS y, EXTRACT(MONTH FROM order_date) AS m, COUNT(*) FROM orders GROUP BY 1, 2;