Humm... So this was my original MSSQL query. It works just fine over in MSSQL land (I no longer need the status_lookup_id portion in the new query).
Which is how I came up with...
WHERE status_lookup_id = 600
datepart(wk,modified_on) = datepart(wk,dateadd(d,-7,getdate()))
year(modified_on) = year(dateadd(d,-7,getdate()))
When I remove the ' from the second query MySQL seems to get pissed. With the goal of all of this being to pull all records with a ada_date (2000-00-00 00:00:00 is the format) from the previous week.
DATE_FORMAT('ada_date','%W') = DATE_FORMAT('date_sub(CURDATE(), INTERVAL 7 DAY)','%W')
YEAR('ada_date') = YEAR('date_sub(CURDATE(), INTERVAL 7 DAY)')