SQL Server: Week start from Thrusday

Question

I want to created a week column as per attached file.

week.PNG

My Week start from Friday-Thursday

I have tried the query. But it gives week1 for dates 1-7 and week2 for dates 8-14.

We want week 1 for 8-14, week 2 for 15-21 and so on

create table #tblUser (createdate date)

--truncate table #tblUser

insert into #tblUser VALUES ('2023-01-01')

insert into #tblUser VALUES ('2023-01-02')

insert into #tblUser VALUES ('2023-01-03')

insert into #tblUser VALUES ('2023-01-04')

insert into #tblUser VALUES ('2023-01-05')

insert into #tblUser VALUES ('2023-01-06')

insert into #tblUser VALUES ('2023-01-07')

insert into #tblUser VALUES ('2023-01-08')

insert into #tblUser VALUES ('2023-01-09')

insert into #tblUser VALUES ('2023-01-10')

insert into #tblUser VALUES ('2023-01-11')

insert into #tblUser VALUES ('2023-01-12')

insert into #tblUser VALUES ('2023-01-13')

insert into #tblUser VALUES ('2023-01-14')

insert into #tblUser VALUES ('2023-01-15')

insert into #tblUser VALUES ('2023-01-16')

insert into #tblUser VALUES ('2023-01-17')

insert into #tblUser VALUES ('2023-01-18')

insert into #tblUser VALUES ('2023-01-19')

insert into #tblUser VALUES ('2023-01-20')

insert into #tblUser VALUES ('2023-01-21')

insert into #tblUser VALUES ('2023-01-22')

insert into #tblUser VALUES ('2023-01-23')

insert into #tblUser VALUES ('2023-01-24')

insert into #tblUser VALUES ('2023-01-25')

insert into #tblUser VALUES ('2023-01-26')

insert into #tblUser VALUES ('2023-01-27')

insert into #tblUser VALUES ('2023-01-28')

insert into #tblUser VALUES ('2023-01-29')

insert into #tblUser VALUES ('2023-01-30')

insert into #tblUser VALUES ('2023-01-31')

insert into #tblUser VALUES ('2023-02-01')

insert into #tblUser VALUES ('2023-02-02')

insert into #tblUser VALUES ('2023-02-03')

insert into #tblUser VALUES ('2023-02-04')

insert into #tblUser VALUES ('2023-02-05')

insert into #tblUser VALUES ('2023-02-06')

insert into #tblUser VALUES ('2023-02-07')

insert into #tblUser VALUES ('2023-02-08')

insert into #tblUser VALUES ('2023-02-09')

insert into #tblUser VALUES ('2023-02-10')

insert into #tblUser VALUES ('2023-02-11')

--select * from #tbluser

SET DATEFIRST 6

SELECT DISTINCT FROM

DATEPART (WEEK, createDate) #tbluser

WHERE createDate

'2023-01-01' AND createDate < '2023-02-11'

ORDER BY DATEPART (WEEK, createDate)

Answer 1

Create a calendar table with week number as you have define for your business logic.