对于日期时间值,您必须非常小心!尤其是一天的指数是很棘手的。您应该始终考虑文化的具体差异:
--The first of January was a Friday in 2016
DECLARE @testDate DATE = {d'2016-01-01'};
--我尝试用德国文化来尝试,从星期一开始
SET LANGUAGE GERMAN;
SELECT @@DATEFIRST,DATEPART(WEEKDAY,@testDate); --in Germany the Friday was 5th day
--现在英国文化也一样,从周日开始
SET LANGUAGE ENGLISH;
SELECT @@DATEFIRST,DATEPART(WEEKDAY,@testDate); --in English culture this is the 6th day
--您可以通过使用 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
现在,两个查询在星期五返回相同的值,6
.
您的示例将星期日显示为一周的第一天,因此给定日期的星期日实际上应该是 7 月 17 日。您的预期产出(7 月 24 日)是下周的第一天,不是吗?
尝试这个:
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)