topic Converting TSQL datepart(week) to Databricks SQL in Data Engineering

Converting TSQL datepart(week) to Databricks SQL

Shahfik — Fri, 25 Aug 2023 09:30:56 GMT

Hi!

I'm converting an existing TSQL script into Databricks SQL.

In TSQL, the below script returns 1
select datepart(WEEK,'2022-01-01')

In Databricks SQL, then below script returns 52.

select date_part('week','2022-01-01')

Does Databricks SQL have something equivalent where Jan 1 is the first week of the year? Thank you in advance.

Re: Converting TSQL datepart(week) to Databricks SQL

Amine — Fri, 25 Aug 2023 10:25:16 GMT

The implementations of date_part and weekofyear functions follow the week numbering specs in ISO 8601. Check the following Wikipedia page for the rules concerning week 52 and 53.

The function returns the value '1' if the day is January 1st and falls on a Monday through Thursday.

Re: Converting TSQL datepart(week) to Databricks SQL

TimFrazer — Thu, 23 Nov 2023 05:16:44 GMT

This worked for me.

SELECT

your_date_column,

CASE

WHEN DAYOFYEAR(your_date_column) <= 7 THEN 'Week 1'

ELSE 'Week ' || CAST(CEIL((DAYOFYEAR(your_date_column) - 7) / 7.0) + 1 AS STRING)

END AS WeekNumber

FROM your_date_table;