Databricks SQL need to set weekofyear to use Sunday as day 1

Question

In SQL, I can simply use:

SET DATEFIRST 7

But I don't know how to do this in Databricks SQL. I am trying to calculate 'Relative Week' to current date and need to use Sunday as the week start day.

My code is:

%sql

SELECT year(calendarDate) * 10000 + month(calendarDate) * 100 + day(calendarDate) as `Date (YYYYMMDD)`,
           calendarDate as `Date`,
           date_format(calendarDate, 'EEEE') as `DayName`,
           weekofyear(calendarDate) as `Week`,
           round(datediff(calendarDate, current_date()) / 7, 0) as `RelativeWeek`
    FROM dates
where calendarDate >= '2022-07-01' and calendarDate < '2022-07-20'

The Sunday week should be 27, not 26.