how to fetch data from past week in sql

问题:

Currently My query is fetching data from current date to past 7 days .I want to fetch data from past week .Means If today is 2013-06-20 then data should be fetched from last week(2013-06-10 to 2013-06-16).I Searched many forum for exact answer but am still unable to find.

Here is my query

 SELECT CONVERT(varchar(25),PostDate,107) AS duration, count(*) AS posts
    FROM MDBDetails WHERE 
      DateAdd(dd, 0, DateDiff(dd, 0, PostDate)) <= DateAdd(dd, 0, DateDiff(dd, 0, GETDATE())) AND 
      DateAdd(dd, 0, DateDiff(dd, 0, PostDate)) >= DateAdd(dd, 0, DateDiff(dd, 0, GETDATE())) - 7

          GROUP BY  CONVERT(varchar(25),PostDate,107)
          ORDER BY duration

回答1:

USE DATEDIFF(week, , )

 SELECT CONVERT(varchar(25),PostDate,107) AS duration, count(*) AS posts
 FROM MDBDetails 
 WHERE DATEDIFF(week, PostDate,GETDATE()) = 1
 GROUP BY  CONVERT(varchar(25),PostDate,107)
 ORDER BY duration

回答2:

The following code should return the monday of the previous week.

SELECT DATEADD(WEEK, -1, DATEADD(WEEK, DATEDIFF(WEEK, 0, SYSDATETIME()), 0));

回答3:

The datepart function can get you the week number. Subtract 1 and you have the previous week.

SELECT DATEPART(WEEK,GETDATE()) -1

Then you can filter your data using week number and year for instance.