----------
20181003
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 Cases
WHERE 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 Cases
WHERE created_at >= '2013-05-01' AND created_at < '2013-05-02'

----------

BETWEEN數值
 
BETWEEN 1 AND 5
 
包含1, 1.01, 1.1, 4.9, 4.99到5.0
 
 
 
BETWEEN字串
 
比如找 S – Z 的BOOK書名
 
BETWEEN 'S' AND 'Z'
 
包含S, SQL, 到Z，但不包含Zero的書名哦!
 
請參考以下的範例說明
 --資料準備
DECLARE @BOOKLIST TABLE
(
ID INT IDENTITY(1,1) 
, BOOK_NAME NVARCHAR(32)
, PRICE DECIMAL(9,3)
, PUBLISH_DATE DATETIME
);
INSERT INTO @BOOKLIST(BOOK_NAME, PRICE, PUBLISH_DATE) VALUES('S', 1, '2011/10/10');
INSERT INTO @BOOKLIST(BOOK_NAME, PRICE, PUBLISH_DATE) VALUES('S1', 1.01, '2011/10/10 10:10');
INSERT INTO @BOOKLIST(BOOK_NAME, PRICE, PUBLISH_DATE) VALUES('S2', 1.111, '2011/12/10 10:10');
INSERT INTO @BOOKLIST(BOOK_NAME, PRICE, PUBLISH_DATE) VALUES('ZERO', 3, '2011/12/10');
INSERT INTO @BOOKLIST(BOOK_NAME, PRICE, PUBLISH_DATE) VALUES('Z', 5.001, '2011/11/10');
INSERT INTO @BOOKLIST(BOOK_NAME, PRICE, PUBLISH_DATE) VALUES('Z1', 5, '2011/11/10 12:10');
INSERT INTO @BOOKLIST(BOOK_NAME, PRICE, PUBLISH_DATE) VALUES('TIGER', 10, '2011/12/10 20:30');

--*****************************************************
--找出單價1~5間的書 (number)
SELECT * FROM @BOOKLIST
WHERE PRICE BETWEEN 1 AND 5
ORDER BY PRICE
--單價大於5的不會找出來


--找出書名S~Z的書 (string)
SELECT * FROM @BOOKLIST
WHERE BOOK_NAME BETWEEN 'S' AND 'Z'
ORDER BY BOOK_NAME
--除了Z，其他Z1, ZERO不會找出來

--那如果要找S-Z的書呢? 那就用 >< 吧
SELECT * FROM @BOOKLIST
WHERE LEFT(BOOK_NAME, 1) >= 'S' AND LEFT(BOOK_NAME, 1) <= 'Z'
ORDER BY BOOK_NAME


--找出 出版日期為2011/10/10~2011/12/10的書 (date)
SELECT * FROM @BOOKLIST
WHERE PUBLISH_DATE BETWEEN '2011/10/10' AND '2011/12/10'
ORDER BY PUBLISH_DATE
--出版日期為2011/12/10 10:10, 2011/12/10 20:30沒有被找出來

--找出 出版日期為2011/10/10 00:00:00 ~ 2011/12/10 23:59:59 的書 (date)
SELECT * FROM @BOOKLIST
WHERE PUBLISH_DATE BETWEEN '2011/10/10 00:00:00' AND '2011/12/10 23:59:59' 
ORDER BY PUBLISH_DATE