Count data from last week in sql server

Question

I'm trying to do a count on all of the data from last week (Monday - Sunday). But it seems like it's ignoring Sunday for some reason and this way I'm not getting the correct count:

    SET DATEFIRST 1
    Select *
    FROM Products a
    inner join Locations b
    on a.Location = b.LocID
    where b.LocID = 12 AND
    a.Created >= DATEADD(week, DATEDIFF(week,0,GETDATE())-1,-1)
    AND a.Created < DATEADD(week, DATEDIFF(week,0,GETDATE()),-1)

Answer 1

Try using day of week instead. I think you might have trouble with using WEEK as you actually need to calculate:

where a.Created >= (last week Monday) and a.Created < (this week Monday)

Here is the query using DW:

SET DATEFIRST 1
Select *
FROM Products a
inner join Locations b
on a.Location = b.LocID
where b.LocID = 12 AND
a.Created >= DATEADD(day, -DATEPART(dw, GETDATE())-6, CONVERT(DATE,GETDATE()))
AND a.Created < DATEADD(day, -DATEPART(dw, GETDATE())+1, CONVERT(DATE,GETDATE()))