Get certain weekday within a week given by a DATETIME

Question

I need to pass one DateTime and DayOfWeek and from that I need to get DateTime of that selected week.

--Sunday-1
--Monday-2
--Tuesday-3
--Wednesday-4
--Thursday-5
--Friday-6
--Saturday-7

DECLARE @DayOfWeek TINYINT = 1
DECLARE @Date DATETIME = '2016-07-21 23:47:11.133'
SELECT DATEADD(wk, DATEDIFF(wk,0,@Date), @DayOfWeek)

for eg:

for this, I need to get date like 2016-07-24 00:00:00 which is Sunday on that week.

Any Ideas?

Answer 1

With datetime values you must be very carefull! Especially the index of a day is tricky. You should always think of culture specific differences:

--The first of January was a Friday in 2016
DECLARE @testDate DATE = {d'2016-01-01'};

--I try this with German culture, this starts with Monday

SET LANGUAGE GERMAN;
SELECT @@DATEFIRST,DATEPART(WEEKDAY,@testDate); --in Germany the Friday was 5th day

--Now the same with English culture, starting on Sunday

SET LANGUAGE ENGLISH;
SELECT @@DATEFIRST,DATEPART(WEEKDAY,@testDate); --in English culture this is the 6th day

--You can get this culture independant by adding those values with Modulo 7

SET LANGUAGE GERMAN;
SELECT (@@DATEFIRST + DATEPART(WEEKDAY,@testDate)) % 7; --in Germany the Friday was 5th day

SET LANGUAGE ENGLISH;
SELECT (@@DATEFIRST + DATEPART(WEEKDAY,@testDate)) % 7; --in English culture this is the 6th day

Now both queries return the same value for Friday, the 6.

Your example shows the Sunday as first day of the week, so the Sunday of the week to the given day should be the 17th of July actually. Your expected output (24th of July) is the first day of the following week, isn't it?

Try this:

DECLARE @DayOfWeek TINYINT = 1;
DECLARE @Date DATETIME = '2016-07-21 23:47:11.133';
SELECT CAST(@Date + @DayOfWeek - (@@DATEFIRST + DATEPART(WEEKDAY,@Date)) % 7 AS DATE)