So if I read this correctly you are looking for events in the last 7 days? Or do you need to be more granular like between specific hours on those days? The way you are doing it seems unnecessarily complex for someone starting out in SQL.
SQL is complaining because it doesn't recognize the string you fed it is supposed to be a datetime format so it can't do relative math operations against it, getting the format right for this can be a pain because it varies based on how your regional settings are and the way dates are displayed on screen can seem kind of misleading. You can try this and it may work:
where [timestamp] > cast('20170625' as date) AND [timestamp] <=cast('20170631' as date)
The way i normally do a lookup for recent dates would be like this
where datediff(day,[timestamp],getdate())< 7
That way I don't have to fuss with figuring out the time formatting and such
TBH I haven't touched any real language since my college/uni days and that was PHP/C++ and other languages that my job never uses. But that was about a decade ago. So my best attempts at trying to even recall how to write a script and MS KB articles was the best I had. Ergo, why you thought it was so massively complex and I thought it was perfectly fine.
And it's all about expanding my knowledge and if I ever get issues like this, I want to at least attempt to resolve my issues and not raise a support call.
First off all, the date format from your output example appears to be dd/mm/yyyy, where as your SQL code is using US style mm/dd/yyyy.
That might be a local thing, but worth checking.
Here is an working example (UTC time format)
SELECT * FROM AlertHistory
WHERE TimeStamp > '2017-06-02' -- this assumes '2017-06-02 00:00:00' or 00:00 2nd of June
AND TimeStamp < '2017-06-03 20:00:00' -- specifying eight o'clock on the 3rd of June 2017
I hope it helps