Converting TSQL datepart(week) to Databricks SQL

Shahfik · ‎08-25-2023

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.

Amine · ‎08-25-2023

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.

Screenshot 2023-08-25 at 12.24.37.png

TimFrazer · ‎11-22-2023

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;