SQL "between" not inclusive

It is inclusive. You are comparing datetimes to dates. The second date is interpreted as midnight when the day starts.

One way to fix this is:

SELECT *FROM CasesWHERE cast(created_at as date) BETWEEN '2013-05-01' AND '2013-05-01'

Another way to fix it is with explicit binary comparisons

SELECT *FROM CasesWHERE 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.

It has been assumed that the second date reference in the BETWEEN syntax is magically considered to be the "end of the day" but this is untrue.

i.e. this was expected:

SELECT * FROM Cases WHERE created_at BETWEEN the beginning of '2013-05-01' AND the end of '2013-05-01'

but what really happen is this:

SELECT * FROM Cases WHERE created_at BETWEEN '2013-05-01 00:00:00+00000' AND '2013-05-01 00:00:00+00000'

Which becomes the equivalent of:

SELECT * FROM Cases WHERE created_at = '2013-05-01 00:00:00+00000'

The problem is one of perceptions/expectations about BETWEEN which does include BOTH the lower value and the upper values in the range, but does not magically make a date the "beginning of" or "the end of".

BETWEEN should be avoided when filtering by date ranges.

Always use the >= AND < instead

SELECT * FROM Cases WHERE (created_at >= '20130501' AND created_at < '20130502')

the parentheses are optional here but can be important in more complex queries.

You need to do one of these two options:

1. Include the time component in your between condition: ... where created_at between '2013-05-01 00:00:00' and '2013-05-01 23:59:59' (not recommended... see the last paragraph)
2. Use inequalities instead of between. Notice that then you'll have to add one day to the second value: ... where (created_at >= '2013-05-01' and created_at < '2013-05-02')

My personal preference is the second option. Also, Aaron Bertrand has a very clear explanation on why it should be used.