Converting TSQL datepart(week) to Databricks SQL

[Shahfik]

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]

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.

[TimFrazer]

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;