SQL Week number is not correct. It seems Saturday is the last day of week-CodePudding

In my website, I got dates and wanted to group by week number. It seems SQL puts the Sunday into the next week number.

SELECT 
    [SelectedDate], (DATEPART(Week, [SelectedDate]) - 1) AS [Week]      
FROM 
    [dbo].[EmployeeTimeSheetModel]

Output:

SelectedDate    Week
-----------------------
2022-10-01      39   ==> Correct. It is the last SATURDAY of the month week 39
2022-10-02      40   ==> Wrong. It is SUNDAY and supposed to be 39
2022-10-03      40   ==> Correct. It is MONDAY and the first day of week 40
2022-10-08      40   ==> Correct. It is SATURDAY and week 40
2022-10-09      41   ==> Wrong. It is SUNDAY and supposed to be 40
2022-10-10      41   ==> Correct. It is MONDAY and the first day of week 41

SQL calculates the week number. I am trying to figure out how to fix this issue, but I need to figure out where to start.

UPDATE I added a CASE Statement to solve my problem, but feel that it is wrong to solve the issue. I am sure there is a better way to do it.

  SELECT [SelectedDate],DATENAME(WEEKDAY,[SelectedDate]), 
  CASE 
      WHEN DATENAME(WEEKDAY,[SelectedDate]) ='Sunday' THEN (DATEPART(Week,[SelectedDate])-2)
      ELSE (DATEPART(Week,[SelectedDate])-1) 
 END AS [Week]   
 FROM [dbo].[EmployeeTimeSheetModel]

CodePudding user response：

you need a other interval. Try iso_week for week.

Select datepart(iso_week,'2022-10-09');

CodePudding user response：

Some software you can choose the 1st day of the week: it can be either Sunday or Monday and it depends on local custom.

So check the software settings - you can usually set it.