无法获取 “中度” 记录,如果年年人不同
问题描述:
我有这种独特的错误无法获取 “中度” 记录,如果年年人不同
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
(注意,今年是相同的)
-I'll得到一些记录
*用于DATE_FORMAT
因为我的日历是给我MM/DD/YYYY
(我不能改变,因为它可能会影响其他地区)
我做错了什么?
答
取而代之的将存储的值转换为其他格式尝试改变参数本身正确的格式。如果你不能做你的网站上你可以通过静态日期,`DATE_FORMAT()`函数做它在您的查询以及
sold_dt BETWEEN
STR_TO_DATE('06/01/2015', '%m/%d/%Y') AND STR_TO_DATE('03/01/2016', '%m/%d/%Y')
+1
非常感谢你,像一个魅力工作 – Bender
答
尝试通过静态日期,DATE_FORMAT()函数
select * from tbl where iscancel = 0 and DATE_FORMAT(sold_dt,"%m/%d/%Y") BETWEEN DATE_FORMAT("06/01/2015","%m/%d/%Y") AND DATE_FORMAT("03/01/2016","%m/%d/%Y") GROUP BY day(sold_dt) order by (sold_dt) asc
尝试。 – sandeepsure
@sandeepsure是的,它正在工作编辑:我试过2015年 - 2016年,但只获得2016年的记录 – Bender
请输入后提供准备的SQL查询。 – sandeepsure