时间筛选查询小于加一天 mysql 中date_add 函数

SELECT
y.totalIntegral totalIntegral,
y.availableIntegral AS availableIntegral,
y.CreateDate as CreateDate
FROM
(
SELECT
a.Money AS totalIntegral ,a.UseableMoney AS availableIntegral,
w.CreateDate as CreateDate


FROM
account a
LEFT JOIN wateraccount w ON a.id = w.AccountId
WHERE
1=1
-- and a.UserName='18601546682'
and a.UserType='承运商'


and date_format(w.CreateDate,'%Y-%m-%d %H:%i:%s') >=date_format('2018-06-10','%Y-%m-%d %H:%i:%s')
 and date_format(w.CreateDate,'%Y-%m-%d %H:%i:%s')< date_format('2018-06-21','%Y-%m-%d %H:%i:%s')+1


 -- and date_format(w.CreateDate,'%Y-%m-%d %H:%i:%s')< DATE_ADD(date_format('2018-06-21','%Y-%m-%d %H:%i:%s'),  interval 1 day)
-- and w.FromMethod='SEND_PRICE_SALE_SCORE'
-- and w.FromApp='承运商APP'
and w.WaterType='IN'


GROUP BY
a.id
ORDER BY w.ModifyDate DESC

) AS y

解析----------------------------------------

date_add 函数用法

DATE_ADD （interval 1 day）

1.加一相当于小于21和等于21的数据查出来

 and date_format(w.CreateDate,'%Y-%m-%d %H:%i:%s')< date_format('2018-06-21','%Y-%m-%d %H:%i:%s')+1

2.不加一只查询小于21的数据查出来等于21的查不出来

 and date_format(w.CreateDate,'%Y-%m-%d %H:%i:%s')< date_format('2018-06-21','%Y-%m-%d %H:%i:%s')

3.相当于小于21和等于21的数据查出来， 与1.相同

-- and date_format(w.CreateDate,'%Y-%m-%d %H:%i:%s')< DATE_ADD(date_format('2018-06-21','%Y-%m-%d %H:%i:%s'),  interval 1 day)

4、小于等于是只查询小于21的 =相当没用

 and date_format(w.CreateDate,'%Y-%m-%d %H:%i:%s')<= date_format('2018-06-21','%Y-%m-%d %H:%i:%s')

5、可以在括号后面加一 与1.相同

 and date_format(w.CreateDate,'%Y-%m-%d %H:%i:%s')<= date_format('2018-06-21','%Y-%m-%d %H:%i:%s')+1