Date comparison in query: Correct logic but wrong result-CodePudding

I have this very simple query. But it's returning wrong result.

select * from `table_a` where date(`schedule`) > '2021-11-13 06:31:00'

In my table, I have these rows

table_a
 --------------------- 
|      schedule       |
 --------------------- 
| 2021-11-13 08:59:00 |
 --------------------- 
| 2021-11-13 08:59:00 |
 --------------------- 
| 2021-11-13 08:59:00 |
 ---------------------

Technically, this query should return all rows. But 0 rows is returned. I tried changing the operation to <and it returned all rows (which is the opposite) . Did I miss something here?

note: schedule datatype is DATETIME.

CodePudding user response：

Your schedule column is datetime, with a time component, so remove the cast to date:

SELECT * FROM table_a WHERE schedule > '2021-11-13 06:31:00';

What was happening is that your 3 records were all being casted to 2021-11-13 at midnight, which is the same as 2021-11-13 00:00:00. It should be clear that all of the 3 records occurred past midnight on this date.