Get data based on a week from a SQL table

To get the day of week, look at datepart:
https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver15

Probably the 'dw' option:  select datepart(dw,getdate());

Can you give an example with the following information:
Tablename: Table1
Columns: Date, Information.

select datepart(dw,date_column), information from table1;

Thanks, but this is not quite I am looking for.
I need the data which falls between a week starting from Monday to Sunday when I pass on the date as a parameter. 

This is the guideline:
​https://learnsql.com/blog/group-data-sql-server/#:~:text=ORDER%20BY%20DATEPART(week%2C%20RegistrationDate,also%20abbreviated%20wk%20%2C%20ww%20).​​​

Well a week to me is the week number of the year so, 1 to 52. Monday to Sunday is the weekday or dayofweek.

datepart(dw,date_value) returns the weed day.  Depending on your locality the number returned by the start of the week can mean different things.  The link you povided mentions DATEFIRST.

Your requirements still aren't all that clear but there seems to be several examples in the learnsql.com link you posted.

What piece is causing you problems?

Sounds like you might be looking for a simple BETWEEN type query but you haven't provided much about your actual requirements.

Sounds like you might be looking for a simple BETWEEN type query but you haven't provided much about your actual requirements.  --Exactly a between query just to check the dates between Monday till Sunday. 

Example:

I want to check all data which was entered between last 2 weeks. Week- Moday to Sunday.
Here the '2' should be a flexible parameter. I might get a request to find data entered 5 weeks a ago.

Thanks

>>i want to check all data which was entered between last 2 weeks. Week- Moday to Sunday.

Your requirements still aren't clear.  All days of the week are between Monday and Sunday.

Between two dates examples are in the link you posted.  Still not sure what parts you are finding confusing.

Please post sample data and expected results from that sample data.

Ok, please find the excel sheet as you requested
DaterangeQuery.xlsx

Still not clear where day of week matters?

From the data you posted, it is a simple between:

select * 
from table1 
where mydate between cast('14-Sep-2020' as date) and cast('27-Sep-2020' as date);

Select allOpen in new window

Fiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ce76c1c6a79fc18e1af795ed583ab028

Hi,
Thanks.
There is line  
So '2' is a parameter for the Stored procedure.
When it is past 2 weeks, the first day is Monday and the week ends on Sunday.

Also you are hard coding the dates. I will get the requirement as past 2 weeks and the dates should be a sql function. Any suggestions? Thanks again 

Create the parameter as a varchar then use dateadd?

This example uses a local variable so just change it to a procdure parameter.

DECLARE @date_var varchar(20);

set @date_var='27-Sep-2020'

select * from table1 where mydate between DATEADD(wk,-2,cast(@date_var as date)) and cast(@date_var as date);

Select allOpen in new window

Fiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0000626ff8e059dad25bf0d6b6f15648

Thanks a lot !
Just a quick one, Is there any option to set week earlier instead of date
**** set @date_var='27-Sep-2020'  

Can it be like Set@Week_Earlier = 4

You can use whatever parameters you want in your procedure.

You will need a date somewhere eventually.  If you want it based off "today", that is the getdate() function I used previously.

So, if the procedure only accepts a weeks-before as a parameter, then use getdate in the procedure and go back that number of weeks.

Great, thanks a lot!

Can you help me in the other sql question?

Probably not.  I'm OK with straight SQL when it comes to SQL Server.  Your other question will probably require a lot more work than simple SQL.

ok, thanks!

Hi,
Can you help me with this :
You can use whatever parameters you want in your procedure.

You will need a date somewhere eventually.  If you want it based off "today", that is the getdate() function I used previously.

So, if the procedure only accepts a weeks-before as a parameter, then use getdate in the procedure and go back that number of weeks.

Apologies , as really tight on deadline and struggling

Starting from the Monday. So we would pass in -6 to go back 6 weeks

The weeks start on Mondays !
Thanks

You have to understand that I don't know your specific requirements.

A procedure has output parameters and/or performs some logic.  I don't know what you want this procedure to do.  You also mentioned a function.  Those return a value.

Are you saying you want a procedure that only accepts a single parameter for weeks-before and use the current date to base the query off of?

Your original question mentioned passing in the start date and number of weeks before that date so, two parameters.

You should have all the pieces on getting rows between dates and subtracting a number of weeks from a date.

What pieces of this task is causing you problems?

>>Starting from the Monday. So we would pass in -6 to go back 6 weeks

Starting from what Monday?

Are you saying you want a procedure that only accepts a single parameter for weeks-before and use the current date to base the query off of? - Exactly this is the question.


Starting from what Monday?  Suppose we have to go 2 weeks back , the Monday 7/Septeber/2020
If it is 4 weeks back it will be 24/August/2020

Hope it answers your queries.

use the current date to base the query off of? - Exactly this is the question.

So basically - 1 parameter ( how many weeks back)
This gives us 1 date till end of this week (Sunday).
Hope it answers your query

You understand what you need this to do but you aren't really giving me enough to provide a copy/paste solution to meet your complete requirements.

for example:
What does this procedure need to do?  Once you select the data, it has to do something with it. I have no idea what that is.

Then you have what seems to be different requirements:
You said you want the query to be based off of the current date then you mention starting on Monday again.

If the select runs today, it will go back 2 weeks from thursday.

If you want the query to ALWAYS start from a Monday, if you run it today, what is the actual date  range for the query you are wanting to pull records for?

What does this procedure need to do?  The procedure with 1 parameter i.e. Weeks to go back so its an number.


If the select runs today, it will go back 2 weeks from thursday. ---
If Select runs today and the parmeter is 2 then the dates will be start date will be 7-Sept-2020 and the end date will be 27-Sept-2020. 

Apologies as the earlier spec were confusing but, will try to answer your queries to make it clear as possible. 

>>What does this procedure need to do?  The procedure with 1 parameter i.e. Weeks to go back so its an number.

Once it queries the rows from the table, what do you need to do with that data?

we have 1 parameter - weeks to go back. example 2
then select query to return the rows in between the two dates.

If Select runs today and the parmeter is 2 then the dates will be start date will be 7-Sept-2020 and the end date will be 27-Sept-2020

So you just want a procedure that only returns a result set, no other business logic at all?

A while back you mentioned passing in negative numbers:
So we would pass in -6 to go back 6 weeks

Are you wanting positive or negative parameters?

A while back you mentioned passing in negative numbers:
So we would pass in -6 to go back 6 weeks

Are you wanting positive or negative parameters?
Its positive number declared as varchar
It was just to explain

Thanks a lot !

So you just want a procedure that only returns a result set, no other business logic at all?
Yes a result set based on the two dates. 

Example : Get an input parameter : 2

The procedure should return a result set based on the dates:
example1 :
If Select runs today and the parmeter is 2 then the dates will be start date will be 7-Sept-2020 (Monday)  and the end date will be 27-Sept-2020(Sunday) .  

example2 :
If Select runs today and the parmeter is 3 then the dates will be start date will be 31-August-2020 (Monday)  and the end date will be 27-Sept-2020(Sunday) .  

example3 :
If Select runs today and the parmeter is 4 then the dates will be start date will be 24-August-2020 (Monday)  and the end date will be 27-Sept-2020(Sunday) .  

Isn't that what Scott's procedure does?  I didn't test it but he knows SQL Server better than I do so I would trust his code.

Oh Dear,
It didn't load . Will check now.

Thanks Scott,
It worked perfectly!
Can't thank you enough!

slightwv (䄆 Netminder) ,
You are a star, helped me a lot!

CORRECTION.  Remove the -1 from the ending date calc, since I am (purposely) using < not <=:

WHERE t1.date >= csd.starting_date AND t1.date < DATEADD(DAY, 7 * (@weeks_before + 1), csd.starting_date)

So the query will be "before Sep 28" rather than "on or before Sep 27".  This is better because then the specific data type of the date column doesn't matter.  

Thanks Scott, will do so.

Hi Scott,
Just a little bit of extention to the result, will appreciate if you help me there:
https://www.experts-exchange.com/questions/29195749/Get-count-of-the-query-result.html#questionAdd