Solved

SQL - pull data from last week

Posted on 2006-07-20
7
968 Views
Last Modified: 2008-02-01
I need a query that pulls data from last week (Monday through Sunday).  It should not matter what day it is now, if it's Monday, or Friday...  I want to only pull data from last week.  

I tried using r.rundate between GetDate()-7 and GetDate(); but this only pulls the data from the last seven days.  I want to pull all of last week.

Thanks in advance!
Scotto13
0
Comment
Question by:Scotto13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17147371
CREATE FUNCTION [dbo].[ufn_GetFirstDayOfWeek] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
    SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
    RETURN DATEADD(DD, 1 - DATEPART(DW, @pInputDate), @pInputDate)
END
GO


DECLARE @today datetime
SET @Today = getdate()

SELECT * FROM urTable
WHERE urdateColumn between [dbo].[ufn_GetFirstDayOfWeek] (@today-7) and
 [dbo].[ufn_GetFirstDayOfWeek] (@today-7) + 7
0
 

Author Comment

by:Scotto13
ID: 17147443
Hi Aneesh,

I only have read access to the database, so I am unable to do Create Function.  Can this be simplified - without the function?
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 17147542

DECLARE @today datetime, @StartOfWeek datetime, @EndOfWeek Datetime

SET @Today = getdate()
SET @Today = CONVERT(VARCHAR(10), @Today-7, 111)
SET @StartOfWeek = DATEADD(DD, 1 - DATEPART(DW, @Today), @Today)
SET @EndOfWeek  = @StartOfWeek + 6

SELECT @StartOfWeek, @EndOfWeek

SELECT * FROM urTable
WHERE urdateColumn between @StartOfWeek AND @EndOfWeek
0
Independent Software Vendors: 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!

 

Author Comment

by:Scotto13
ID: 17147618
OK, Aneesh.  I have thre query running in SQL.  Now my solution calls for a SQL Pass-through query in MS Access.  The query won't run in Access.  I can't figure out why.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17147638
I have no idea on Access databases, may be you can post in the MS Access area
0
 

Author Comment

by:Scotto13
ID: 17147645
ok.  Thanks for the good solution.
0
 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 17147673
Try this to combine everything into single statement:

SELECT * FROM urTable
WHERE urdateColumn between DATEADD(DD, 1 - DATEPART(DW, @CONVERT(VARCHAR(10), getdate()-7, 111)
), @CONVERT(VARCHAR(10), getdate()-7, 111)) AND DATEADD(DD, 1 - DATEPART(DW, @CONVERT(VARCHAR(10), getdate()-7, 111)
), @CONVERT(VARCHAR(10), getdate()-7, 111)) + 6
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

728 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