For larger date ranges also, it is (inclusive) i.e. work with a single date too. You are trying to compare datetimes to dates. But, the second date is interpreted as midnight when the day starts.
Use the below code to fix it:
WHERE cast(created_at as date) BETWEEN '2013-05-01' AND '2013-05-01'
Otherwise, you can do explicit binary comparisons like this:
WHERE created_at >= '2013-05-01' AND created_at < '2013-05-02'
Aaron Bertrand has a long blog entry on dates (here), where he discusses this and other date issues.