在DATETIME给定的一周内获得一定的工作日 [英] Get certain weekday within a week given by a DATETIME

问题描述

我需要通过一个 DateTime 和 DayOfWeek ，从那里我需要得到日期时间

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)

例如：

为此，我需要得到这样的日期，例如 2016-07-24 00:00:00 这是星期日

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

任何想法？

推荐答案

使用datetime值非常小心！特别是一天的指数是棘手的。你应该总是想到文化的具体差异：

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

- 你可以通过添加这些文字来获得这种文化独立值为7的

--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

现在，这两个查询在星期五返回相同的值， 6 。

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

您的示例显示了星期日作为一周的第一天，所以星期几到给定的一天应该是7月17日。您的预期输出（7月24日）是下周的第一天，不是吗？

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?

尝试这样：

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)

这篇关于在DATETIME给定的一周内获得一定的工作日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！