0

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.

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()))
Bugs
  • 4,491
  • 9
  • 32
  • 41
Rabeea qabaha
  • 526
  • 8
  • 23

4 Answers4

4

Take a look at SET DATEFIRST on MS Docs.

Sets the first day of the week to a number from 1 through 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

McNets
  • 10,352
  • 3
  • 32
  • 61
  • Hello, thanks for your answer, actually it should from number 0 to 7, edit your answer to make it the answer. – Rabeea qabaha Jan 18 '17 at 16:36
  • are you sure it runs 0-7? That would suggest 8 different settings - what does 0 do? Maybe it does something - but in any case, you would not need it since the answer covers all days. – Cato Jan 18 '17 at 17:23
  • if i put it form 1 the first Saturday not shown, please if you can try it try it and told me – Rabeea qabaha Jan 18 '17 at 17:28
  • @Cato it uses -8 I suppouse because he uses < date_fin, -7 requires <= – McNets Jan 18 '17 at 18:09
  • @Rabeeaqabaha, what do you mean? If you `SET DATEFIRST 1` then first day of week is Monday. In the current example it returns 'Monday 16-01-2017', – McNets Jan 18 '17 at 18:13
  • What i want is showing data from Saturday to Friday, so if i make it 1-7 its showing from Saturday to Saturday i don't know why, but if i make it from 0 to 7 its showing what i want , but I'm afraid that I'm wrong :( – Rabeea qabaha Jan 19 '17 at 03:09
1

By default the week will start from sunday. To change it use 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 

+---------------------------+--------------------------+
|           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                   |
+---------------------------+--------------------------+
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
0

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

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)
Wagner DosAnjos
  • 6,304
  • 1
  • 15
  • 29
0

try this example

SELECT count(AddTime) AS time,
   CASE
       WHEN (weekday(AddTime)<=3) THEN date(AddTime + INTERVAL (3-weekday(AddTime)) DAY)
       ELSE date(AddTime + INTERVAL (3+7-weekday(AddTime)) DAY)
   END AS week_days
FROM SAAS_Appoint
WHERE Status = 2
AND AddTime > "2020-01-01 00:00:00"
GROUP BY week_days;

$weekArr = array( 'Monday' => 0, 'Tuesday' => 1, 'Wednesday' => 2, 'Thursday' => 3, 'Friday' => 4, 'Saturday' => 5, 'Sunday' => 6);

the example is start from friday and end at thusrday。 just replace 3 to any day you like。

cathy_mu
  • 38
  • 5