我在mysql中遇到了过滤日期时间的问题

Question

我有这样的疑问：

select b.observation, b.created_date (datetime field)
FROM logistics_wms.reception_documents a
join logistics_wms.reception b on a.reception_id = b.id
join logistics_wms.document c on a.documents_id = c.id
join logistics_wms.document_type d on d.id = c.document_type_id
join logistics_wms.document_line e on c.id = e.document_id
join logistics_wms.product g on e.product_id = g.id
where b.status = 'SUBMITTED'
and b.created_date >= '2020-04-01 00:00:00'

我也用了'2020-04-01‘

结果是：

​

​

但是如果我使用：

select b.observation, b.created_date (datetime field)
FROM logistics_wms.reception_documents a
join logistics_wms.reception b on a.reception_id = b.id
join logistics_wms.document c on a.documents_id = c.id
join logistics_wms.document_type d on d.id = c.document_type_id
join logistics_wms.document_line e on c.id = e.document_id
join logistics_wms.product g on e.product_id = g.id
where b.status = 'SUBMITTED'
and b.created_date >= '2020-04-15 00:00:00'

我也用了'2020-04-15‘

结果是：

​

​

为什么当我选择2020-04-01时，查询没有得到第二个查询的最后两条记录？？！！

请帮帮我

谢谢

Answer 1

鉴于时间对于您的情况(00:00:00)并不重要，在您的过滤器中使用MySQL日期函数要简单得多。您可以尝试使用：where b.status = 'SUBMITTED' and DATE(b.created_date) >= '2020-04-01'和where b.status = 'SUBMITTED' and DATE(b.created_date) >= '2020-04-15'吗