Description:
MySQL [test1]> insert into chqin values(1,'2020-01-01');
Query OK, 1 row affected (0.01 sec)
MySQL [test1]> insert into chqin values(1,'2019-03-21');
Query OK, 1 row affected (0.00 sec)
Here, it should return 'Empty set'.
MySQL [test1]> select t1.col_date from chqin t1 where t1.col_date NOT BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ;
+------------+
| col_date |
+------------+
| 2020-01-01 |
| 2019-03-21 |
+------------+
2 rows in set, 2 warnings (0.00 sec)
---
MySQL [test1]>
---Here, it works fine with the const value '2020-01-01'.
MySQL [test1]> select 1 from chqin t1 where '2020-01-01' NOT BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ;
Empty set, 3 warnings (0.00 sec)
MySQL [test1]>
MySQL [test1]> select t1.col_date from chqin t1 where t1.col_date BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ;
Empty set, 2 warnings (0.00 sec)
---Here, when I use NULL instead of STR_TO_DATE('2019-03-20', 'YYYY-MM-DD HH24:MI:SS'), it functions correctly and returns an empty result.
MySQL [test1]> select t1.col_date from chqin t1 where t1.col_date NOT BETWEEN '2019-03-20' AND null ;
Empty set (0.00 sec)
How to repeat:
drop table chqin;
create table chqin(id int, col_date date);
insert into chqin values(1,'2020-01-01');
insert into chqin values(1,'2019-03-21');
select t1.col_date from chqin t1 where t1.col_date NOT BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ;
select 1 from chqin t1 where '2020-01-01' NOT BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ;
select t1.col_date from chqin t1 where t1.col_date BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ;
select t1.col_date from chqin t1 where t1.col_date NOT BETWEEN '2019-03-20' AND null ;
Description: MySQL [test1]> insert into chqin values(1,'2020-01-01'); Query OK, 1 row affected (0.01 sec) MySQL [test1]> insert into chqin values(1,'2019-03-21'); Query OK, 1 row affected (0.00 sec) Here, it should return 'Empty set'. MySQL [test1]> select t1.col_date from chqin t1 where t1.col_date NOT BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ; +------------+ | col_date | +------------+ | 2020-01-01 | | 2019-03-21 | +------------+ 2 rows in set, 2 warnings (0.00 sec) --- MySQL [test1]> ---Here, it works fine with the const value '2020-01-01'. MySQL [test1]> select 1 from chqin t1 where '2020-01-01' NOT BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ; Empty set, 3 warnings (0.00 sec) MySQL [test1]> MySQL [test1]> select t1.col_date from chqin t1 where t1.col_date BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ; Empty set, 2 warnings (0.00 sec) ---Here, when I use NULL instead of STR_TO_DATE('2019-03-20', 'YYYY-MM-DD HH24:MI:SS'), it functions correctly and returns an empty result. MySQL [test1]> select t1.col_date from chqin t1 where t1.col_date NOT BETWEEN '2019-03-20' AND null ; Empty set (0.00 sec) How to repeat: drop table chqin; create table chqin(id int, col_date date); insert into chqin values(1,'2020-01-01'); insert into chqin values(1,'2019-03-21'); select t1.col_date from chqin t1 where t1.col_date NOT BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ; select 1 from chqin t1 where '2020-01-01' NOT BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ; select t1.col_date from chqin t1 where t1.col_date BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ; select t1.col_date from chqin t1 where t1.col_date NOT BETWEEN '2019-03-20' AND null ;