1、背景
工人类(ProjectWorker)有一类型为date的进场时间(entrytime):
private Date entrytime;
2、前台页面传过来两个过滤时间,通过两个属性来接收:
privateString startDate;private String endDate;
3、mysql中查询
and a.entrytime>=#{startDate}
and a.entrytime<=#{endDate}
4、结果展示:
4.1、 如果entrytime = 2019-09-12 00:00:00 ,startDate="2019-09-12", endDate="2019-09-12",
SELECT
a.id,
a. NAME,
a.sex,
a.nation,
a.entrytime
FROM
t_project_worker a
WHERE
a.do_id = 7
AND a.entrytime >= '2019-09-12'
AND a.entrytime<= '2019-09-12'
4.2、 如果entrytime = 2019-09-12 00:00:01 ,startDate="2019-09-12", endDate="2019-09-12",
5、修改SQL
SELECT
a.id,
a. NAME,
a.sex,
a.nation,
a.entrytime
FROM
t_project_worker a
WHERE
a.do_id = 7
AND a.entrytime >= '2019-09-12 00:00:00'
AND a.entrytime<= '2019-09-12 23:59:59'
或者:
SELECT
a.id,
a. NAME,
a.sex,
a.nation,
a.entrytime
FROM
t_project_worker a
WHERE
a.do_id = 7
AND DATE_FORMAT(a.entrytime,'%Y-%m-%d') >= '2019-09-12'
AND DATE_FORMAT(a.entrytime,'%Y-%m-%d')<= '2019-09-12'
6、结论:
mySQL中如果传入一个年月日字符串当作日期,则默认为该日期的零点零分零秒。