Solved

Get Tuesday through Monday in query

Posted on 2013-10-22
3
290 Views
Last Modified: 2013-10-30
I want to pull data from last week to this week starting on Tuesday of last week through the next Monday (of the current week).
0
Comment
Question by:williamss132
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39592233
Here's the code to get the current day of the week (1=Sunday to 7-Saturday)
SELECT DATEPART(dw, GETDATE()) 

Open in new window

So, doing a little math...
Declare @dt date = '10-25-13'

SELECT DATEADD(d,  - (DATEPART(dw, @dt) + 2), @dt) as last_thursday,
	DATEADD(d,  - (DATEPART(dw, @dt) - 2), @dt) as this_monday 

Open in new window

btw Here's an article I wrote on How to build your own SQL Calendar Table that demonstrates lots of goofy-riffic date expressions you can use.
0
 
LVL 9

Expert Comment

by:COANetwork
ID: 39592281
Here is how you can determine the most recent Tuesday:
DECLARE @myTuesdayDate date, @myMondayDate date;
SELECT @myTuesdayDate = DATEADD(DAY, DATEDIFF(DAY, 2, GETDATE()) / 7 * 7, 1);

Open in new window

Assign this to a date variable, then do a
SELECT @myMondayDate = DATEADD(day, 6, @myTuesdayDate)

Open in new window

and you get your next Monday.  Then use these values as a range for your select
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39611684
Thanks for the grade.  Good luck with your code.  -Jim
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question