I use this query on SEDE. I have just been editing the WHERE p.CreationDate > '2014-12-21T00:00:00.001' like every week to select the last week's records only. So for example previous edits were just changing to 12-21 from 12-14 from 12-7, etc..
I'm trying to edit that part so that I don't have to keep editing it every week.
I was thinking I could do something like
WHERE DATEDIFF(DAY, p.creationDate, GETDATE()) <= 7
which would select only results from the last 7 days.
However, this will only work on like Sunday when SEDE is updated. If I run a query on Wednesday, then this query will be missing three days of results.
How could I write this where statement to like find the last nearest Sunday, or is Sunday, and then go back one week from there?
To find previous sunday use this piece of code.
select DateAdd(dd, -1, DateAdd(wk, DateDiff(wk, 0, getdate()), 0)) [Previous Sunday]
Where clause should be something like.
Select ... from tablename
WHERE DATEDIFF(DAY, p.creationDate, DateAdd(dd, -1, DateAdd(wk, DateDiff(wk, 0, getdate()), 0))) <= 7