Select Between Date Range Mysql

Question

I want to select records between 2 dates:

select * from orders where orders.created  <= '2015-07-31';
 select * from orders where orders.created >= '2015-07-01' and orders.created  <= '2015-07-31';

No results. If I do:

select * from orders where date(orders.created) >= '2015-07-01' and date(orders.created)  <= '2015-07-31';

Many results appear.Why?

mysql> select id,identifier,created from orders where date(orders.created) >= '2015-07-01' and date(orders.created)  <= '2015-07-31';
+-----+--------------+---------------------+
| id  | identifier   | created             |
+-----+--------------+---------------------+
| 366 | eu-126123916 | 2015-07-31 16:58:22 |
| 367 | eu-126127446 | 2015-07-31 17:43:14 |
| 368 | eu-126127510 | 2015-07-31 17:43:52 |
| 370 | eu-126134761 | 2015-07-31 18:56:07 |
| 371 | eu-126135814 | 2015-07-31 19:05:46 |
| 372 | eu-126144955 | 2015-07-31 20:34:48 |
+-----+--------------+---------------------+
6 rows in set (0.00 sec)


mysql> describe orders;
+---------------------+---------------------+------+-----+---------+----------------+
| Field               | Type                | Null | Key | Default | Extra          |
+---------------------+---------------------+------+-----+---------+----------------+
| id                  | bigint(19) unsigned | NO   | PRI | NULL    | auto_increment    
| total               | float               | YES  |     | NULL    |                |
| totaltax            | float               | YES  |     | NULL    |                |
| totalpaid           | float               | YES  |     | NULL    |                |
| ticket              | varchar(45)         | YES  |     | NULL    |                |
| paypal_id           | varchar(45)         | YES  |     | NULL    |                |
| note                | text                | YES  |     | NULL    |                |
| totaldocuments      | int(4)              | YES  |     | NULL    |                |
| sent_to_operator    | varchar(1024)       | YES  |     | NULL    |                |
| created             | datetime            | YES  |     | NULL    |                |
| modified            | datetime            | YES  |     | NULL    |                |


22 rows in set (0.01 sec)

My engine is TokuDB.

Answer 1

In your first query you do ...orders.created <= '2015-07-31'; which is actually

orders.created  <= '2015-07-31 00:00:00';

since you have a timestamp or datetime datatype. Either use the DATE() function like you did in your other attempt, or write it like

orders.created  <= '2015-07-31 23:59:59';

Answer 2

Better yet:

where orders.created >= '2015-07-01'
  and orders.created  < '2015-07-01' + INTERVAL 1 MONTH;

This works for DATE, DATETIME, DATETIME(6), leap year, etc. (Notice the <, not <=.)