SQL Server Select last 4 weeks from now and same period from last year only
You can use some more DATEADD()
goodness to go back to the previous year:
where theDate >= DATEADD(mm, -1, GETDATE())
OR
(theDate <= DATEADD(week,-52,convert(datetime,GETDATE()))
and
theDate >= DATEADD(mm,-1,DATEADD(week,-52,convert(datetime,GETDATE()))))
Subtracting 52 weeks from 6/30/2011 returns 7/1/2010 as you requested... Then using your original subtraction of 1 month from there for the lower bound.
You could also switch the whole thing to use weeks...
where theDate >= DATEADD(week, -4, GETDATE())
OR
(theDate <= DATEADD(week,-52,convert(datetime,GETDATE()))
and
theDate >= DATEADD(week,-56,convert(datetime,GETDATE())))