CODEWITHSUNDEEP
sql-serverselect
Rabeea qabaha
Rabeea qabaha

Reputation: 606

How to get data of current week Start on Saturday end in Friday from SQL server

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

Upvotes: 0

Views: 7970

Answers (4)

cathy_mu
cathy_mu

Reputation: 38

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。

Upvotes: 0

McNets
McNets

Reputation: 10827

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

Upvotes: 4

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

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)

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

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

Upvotes: 1

Related Questions