如何获取当前周的数据从星期五星期六开始从SQL服务器开始

how can I get the data from sql server for current week starting in Saturday ending in Friday, so select all data from Saturday to Friday for the current week.

如何从星期六星期六结束的当周开始从sql server获取数据，因此选择本周的星期六到星期五的所有数据。

I found this code but started in Sunday and I can't change it:

我发现这个代码但是在星期天开始，我无法改变它：

where Date >= dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) AND Date <  dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))

3 个解决方案

#1

Take a look at SET DATEFIRST on MS Docs.

在MS Docs上查看SET DATEFIRST。

Sets the first day of the week to a number from 1 through 7.

将一周的第一天设置为1到7之间的数字。

Where:

哪里：

1   Monday
2   Tuesday
3   Wednesday
4   Thursday
5   Friday
6   Saturday
7   Sunday (default, U.S. English)

Have a look at next example:

看看下一个例子：

DECLARE @CurrentDate DATETIME;
SET @CurrentDate = CONVERT(DATETIME,'2017-01-18');

SET DATEFIRST 1
SELECT DATEADD(day, 1 - DATEPART(dw, @CurrentDate), @CurrentDate);
RETURNS '2017-01-16' (Monday)

SET DATEFIRST 2
SELECT DATEADD(day, 1 - DATEPART(dw, @CurrentDate), @CurrentDate);
RETURNS '2017-01-17' (Tuesday)

SET DATEFIRST 3
SELECT DATEADD(day, 1 - DATEPART(dw, @CurrentDate), @CurrentDate);
RETURNS '2017-01-18' (Wednesday)

SET DATEFIRST 4
SELECT DATEADD(day, 1 - DATEPART(dw, @CurrentDate), @CurrentDate);
RETURNS '2017-01-12' (Thursday)

SET DATEFIRST 5
SELECT DATEADD(day, 1 - DATEPART(dw, @CurrentDate), @CurrentDate);
RETURNS '2017-01-13' (Friday)

SET DATEFIRST 6
SELECT DATEADD(day, 1 - DATEPART(dw, @CurrentDate), @CurrentDate);
RETURNS '2017-01-14' (Saturday)

SET DATEFIRST 7
SELECT DATEADD(day, 1 - DATEPART(dw, @CurrentDate), @CurrentDate);
RETURNS '2017-01-15' (Monday)

You can check it here: http://rextester.com/YSGVM53271

您可以在此处查看：http：//rextester.com/YSGVM53271

#2

By default the week will start from sunday. To change it use DATEFIRST.

默认情况下，本周将从周日开始。要更改它，请使用DATEFIRST。

SET DATEFIRST 6

WHERE  Date >= Cast(Dateadd(dd, -Datepart(WEEKDAY, Getdate()) + 1, Getdate()) AS DATE)
       AND Date < Cast(Dateadd(dd, 7 - Datepart(WEEKDAY, Getdate()) + 1, Getdate()) AS DATE)

More info on DATEFIRST

有关DATEFIRST的更多信息

+---------------------------+--------------------------+
|           Value           | First day of the week is |
+---------------------------+--------------------------+
| 1                         | Monday                   |
| 2                         | Tuesday                  |
| 3                         | Wednesday                |
| 4                         | Thursday                 |
| 5                         | Friday                   |
| 6                         | Saturday                 |
| 7 (default, U.S. English) | Sunday                   |
+---------------------------+--------------------------+

#3

You can try the following. It works regardless of the SET DATEFIRST value:

您可以尝试以下方法。无论SET DATEFIRST值如何，它都有效：

where [Date] >= CAST(DATEADD(d, -(@@DATEFIRST + DATEPART(dw, GETDATE())) % 7, @d) as DATE)
  and [Date] < CAST(DATEADD(d, -(@@DATEFIRST + DATEPART(dw, GETDATE())) % 7 + 6, @d) as DATE)

#1