Get data of current week start on saturday end in friday

Question

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 22-Jan-17 4:34am

Rabee Qabaha

Updated 22-Jan-17 8:41am

Add a Solution

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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RossMW · Answer 1 · 2017-01-22T08:41:00

Solution 1

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

Posted 22-Jan-17 8:41am

RossMW

Updated 22-Jan-17 8:43am

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)

Member 9831003 · Answer 2 · 2017-01-24T01:16:00

Solution 2

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

Posted 24-Jan-17 1:16am

Member 9831003

Updated 24-Jan-17 1:26am

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