Date Validation in MySQL

Question

Can anyone help me on how did MySQL can't read a rows of I've try to execute this query.

SELECT * FROM attendance where '2010-07-13 00:06:00' BETWEEN timein AND timeout;

This is the date in the table:

+-----------------------+-----------------------+
|  timein               |  timeout              |
------------------------+------------------------
|  2010-07-13 23:44:11  |  2010-07-14 08:01:14  |
|  2010-07-12 23:40:56  |  2010-07-13 08:00:52  |   
|  2010-07-10 05:49:32  |  2010-07-10 14:00:45  |
+-----------------------+-----------------------+

as we can see on the table, row 2 is expected to meet the date validation but when I execute the query, it returns no result. Can anyone help if there was an alternative queries to do it.

Answer 1

Your query is fine. It should work as expected:

CREATE TABLE attendance (id int, timein datetime, timeout datetime);

INSERT INTO attendance VALUES (1, '2010-07-13 23:44:11', '2010-07-14 08:01:14');
INSERT INTO attendance VALUES (2, '2010-07-12 23:40:56', '2010-07-13 08:00:52');
INSERT INTO attendance VALUES (3, '2010-07-10 05:49:32',' 2010-07-10 14:00:45');

SELECT * FROM attendance where '2010-07-13 00:06:00' BETWEEN timein AND timeout;
+------+---------------------+---------------------+
| id   | timein              | timeout             |
+------+---------------------+---------------------+
|    2 | 2010-07-12 23:40:56 | 2010-07-13 08:00:52 |
+------+---------------------+---------------------+
1 row in set (0.01 sec)

Are you sure that your timein and timeout fields are of type datetime or timestamp?

---

UPDATE: Further to @Psytronic's suggestion the comments below, your example would even work if your fields were of varchar type:

CREATE TABLE attendance (id int, timein varchar(100), timeout varchar(100));

INSERT INTO attendance VALUES (1, '2010-07-13 23:44:11', '2010-07-14 08:01:14');
INSERT INTO attendance VALUES (2, '2010-07-12 23:40:56', '2010-07-13 08:00:52');
INSERT INTO attendance VALUES (3, '2010-07-10 05:49:32',' 2010-07-10 14:00:45');

SELECT * FROM attendance where '2010-07-13 00:06:00' BETWEEN timein AND timeout;
+------+---------------------+---------------------+
| id   | timein              | timeout             |
+------+---------------------+---------------------+
|    2 | 2010-07-12 23:40:56 | 2010-07-13 08:00:52 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)

However your fields should not be varchar, as the above would be doing a string comparison instead of a time comparison.

Answer 2

You'll want to do something like:

SELECT * FROM attendance where timein >= '2010-07-13 00:06:00' and timeout <= '2010-07-13 17:00:00';

EDIT: I defer to the experts here, but this is how I would do it.