SQL - Getting day for the whole week

Question

I want to get the whole day of the week depend on the date, my query is working and getting the result that I want but when the date value is Sunday, result changes. I'm starting the result from Mon to Sunday. Examples below:

My Code:

 SELECT UserID,Scdl_TkIN as TimeIn, Scdl_TkOut as  [TimeOut] 
   FROM EmployeeTimekeeping 
   WHERE CONVERT(DATE,Scdl_TkIN) >= dateadd(day, 2-datepart(dw, '2022-04-23'),CONVERT(date,'2022-04-23')) 
    AND CONVERT(DATE,Scdl_TkIN) <  dateadd(day, 9-datepart(dw, '2022-04-23'), CONVERT(date,'2022-04-23'))AND UserID ='15020009'
    ORDER BY CONVERT(DATE,Scdl_TkIN)

1st display is correct, but when I change the value into '2022-04-24' , the result is now the second pic but I want the result still 1st pic.

Answer 1

If I got it right you want the whole week of data given a single date.

I'm not 100% sure about your date logic and I'd rather use the WEEK as a filter as it seems clearer, that said the issue you have is the value of SELECT @@DATEFIRST. By default its value is 7, meaning that Sunday is considered the first day of the week, that's why you get that "unexpected" result.

here is my solution, but just setting SET DATEFIRST 1; should give you the expected result.

SET DATEFIRST 1;
SELECT
   UserID
  ,Scdl_TkIN as TimeIn
  ,Scdl_TkOut as TimeOut
FROM EmployeeTimekeeping 
WHERE
  DATEPART(WEEK,Scdl_TkIN) = DATEPART(WEEK,'2022-04-23')
  AND YEAR(Scdl_TkIN) = YEAR('2022-04-23')
  AND UserID ='15020009'
ORDER BY
  Scdl_TkIN

Note: if you decide to use WEEK for filtering you will have to choose between WEEK and ISO_WEEK Edit: when using week you must also consider the year in the filter