php - 如果年份不同，则无法获取 "inbetween"记录

Question

我遇到了这个独特的错误

TABLE : tbl
id | title | iscancel | sold_dt

id: UID
Title : varchar
iscancel : 0/1
sold_dt : timestamp


select * from tbl where iscancel = 0 and DATE_FORMAT(sold_dt,"%m/%d/%Y") BETWEEN "06/01/2015" AND "03/01/2016" GROUP BY day(sold_dt) order by (sold_dt) asc 

(注意年份不一样)

• 返回 0 条记录

但如果我这样做

select * from tbl where iscancel = 0 and DATE_FORMAT(sold_dt,"%m/%d/%Y") BETWEEN "06/01/2015" AND "12/01/2015" GROUP BY day(sold_dt) order by (sold_dt) asc 

或

select * from tbl where iscancel = 0 and DATE_FORMAT(sold_dt,"%m/%d/%Y") BETWEEN "01/01/2016" AND "03/01/2016" GROUP BY day(sold_dt) order by (sold_dt) asc 

(注意年份相同)

-我会得到一些记录

*使用 DATE_FORMAT 因为我的日历给我 MM/DD/YYYY(我无法更改，因为它可能会影响其他区域)

我做错了什么？

Answer 1

与其将存储的值转换为另一种格式，不如尝试将参数本身更改为正确的格式。如果您不能在您的网站上执行此操作，您也可以在您的查询中执行此操作

sold_dt BETWEEN
STR_TO_DATE('06/01/2015', '%m/%d/%Y') AND STR_TO_DATE('03/01/2016', '%m/%d/%Y')