• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 979
  • Last Modified:

SQL - pull data from last week

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
Scotto13
Asked:
Scotto13
  • 3
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Scotto13Author Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:

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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Scotto13Author Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
I have no idea on Access databases, may be you can post in the MS Access area
0
 
Scotto13Author Commented:
ok.  Thanks for the good solution.
0
 
Mr_PeerapolCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now