SQL Code to get Week Number when week begins Sunday to Saturday

Question

My company’s week begins on a Sunday. The issue I’m having is extracting the week number from the date in the database.

For example,02/01/2022 falls in week 52 of 2021 using the Sunday week beginning logic. How can I write a SQL code to give me :

a) the week number (I.e 2nd Jan 2022 is part of week 52)

b) the correct year of the week. i.e 2nd Jan 2022 , I want it to bring back the year “2021” so my data set is complete and accurate.

N.b. Database name I’m using is dw-fin and column of the date is called date-created

Answer 1

You can use the WEEK function with a 'mode'.
Which is an integer indicating the starting of the week.
And the YEARWEEK function also has a mode.

Mode	First day of week	Range	Week 1 is the first week
0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with 4 or more days this year
2	Sunday	1-53	with a Sunday in this year
3	Monday	1-53	with 4 or more days this year
4	Sunday	0-53	with 4 or more days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	1-53	with 4 or more days this year
7	Monday	1-53	with a Monday in this year

Example :

select date_column
, month(date_column) as month
, year(date_column) as year
, yearweek(date_column, 2) as mode2_yearweek
, floor(yearweek(date_column, 2)/100) as mode2_year
, week(date_column, 2) as mode2_week
from (select date('2022-01-01') as date_column) q;

date_column	month	year	mode2_yearweek	mode2_year	mode2_week
2022-01-01	1	2022	202152	2021	52

Demo on db<>fiddle here