Click here to Skip to main content
15,065,644 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
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:

SQL
SELECT TOP (200) id, p_id, p_name, s_quantity, s_p_price, s_price, cost_price, profit, date, note, p_delete FROM filters_sold
WHERE (date >= DATEADD(day, 0 - DATEPART(dw, GETDATE()), CONVERT(date, GETDATE()))) AND (date < DATEADD(day, 7 - DATEPART(dw, GETDATE()), CONVERT(date, GETDATE())))


what i want lets say where are in this week and today is Sunday 22/1/2017
i want to get all data from Saturday 21/1/2017 to Friday 27/1/2017.

then when the next week come lets say the next Saturday 28/1/2017 i want to get the current week also start in Saturday 28/1/2017 ending in Friday 3/2/2017and like so ..

the code above get the data from Saturday to Friday but the beginning of the week is Sunday how can i make it to Saturday

What I have tried:

<pre lang="SQL">SELECT TOP (200) id, p_id, p_name, s_quantity, s_p_price, s_price, cost_price, profit, date, note, p_delete FROM filters_sold
WHERE (date >= DATEADD(day, 0 - DATEPART(dw, GETDATE()), CONVERT(date, GETDATE()))) AND (date < DATEADD(day, 7 - DATEPART(dw, GETDATE()), CONVERT(date, GETDATE())))
Posted
Updated 22-Jan-17 8:41am
Comments
Richard MacCutchan 22-Jan-17 10:40am
   
Just change the day number values to calculate from Saturday instead of from Sunday.
Rabee Qabaha 22-Jan-17 10:48am
   
I tried but i couldn't can you show me how please
Richard MacCutchan 22-Jan-17 11:04am
   
I don't have an SQL instance to try it on so you will have to work it out yourself by experimentation. Just change the 0 and the 7 in the above statements to the necessary values and run some tests to check they are correct. There are only one or two combinations to try.
Rabee Qabaha 22-Jan-17 11:06am
   
I tried but I couldn't make it, thank you

I think the problem is in the field type. If it is datetime it will include a time value and hence you will need to filter where the date is greater than the end of the day before. Otherwise the time value will cause all the saturday dates to be excluded

eg
day > last friday and day < next saturday

ie

date > dateadd(day, - datepart(dw,getdate()) -1, getdate()) and Date < dateadd(day, 7 - datepart(dw,getdate()), getdate()) 
   
v4
Comments
Rabee Qabaha 23-Jan-17 10:12am
   
Yes the date field is Datetime so do i need cast ??
or just use your code ??
RossMW 23-Jan-17 13:07pm
   
Assuming the date field is datetime then everything is in a date format and no need to cast. Experimentation is your friend (in a test system)
Not sure whether my answer will help your query but ....try this

Set your first day of week according to your choice. The default setting for us_english is 7 (Sunday)

Just set in query

SET DATEFIRST 1;

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

Thanks
   
Comments
Rabee Qabaha 25-Jan-17 18:13pm
   
@Member 9831003 Hello until now i couldn't fiend a solution, can you help me ??
please show me how to do this in code

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900