mysql年月日时分秒格式比较_MySQL：年月日时分秒 与 年月日 区间比较

system_order 中有 字段

APPLY_START_DATE   2018-03-06 00:00:00

START_DATE  2018-03-06

REFUSAL_REASON    RF001

SELECT COUNT(*) FROM system_orderWHERE province = '澳门'

AND START_DATE >= '2018-03-06'

AND START_DATE <= '2018-03-06'

AND REFUSAL_REASON LIKE 'RF%'

结果为1

SELECT COUNT(*) FROM system_order

WHERE province = '澳门'

AND APPLY_START_DATE >= '2018-03-06'

AND APPLY_START_DATE <= '2018-03-06'

AND REFUSAL_REASON LIKE 'RF%'

结果为0

SELECT COUNT(*) FROM system_order

WHERE province = '澳门'

AND TO_DAYS(APPLY_START_DATE) >= TO_DAYS('2018-03-06')

AND TO_DAYS(APPLY_START_DATE) <= TO_DAYS('2018-03-06')

AND REFUSAL_REASON LIKE 'RF%'结果为1

因为SELECT TO_DAYS('2018-03-06')

等于 737124

SELECT TO_DAYS('2018-03-06 00:00:00')   也等于 737124

mybatis 的 mapper.xml中可以这样写

select count(*) from system_order

and province = #{province}

and product_channel = #{product_channel}

and TO_DAYS(apply_start_date) = ]]> TO_DAYS(#{start_date})

and TO_DAYS(apply_start_date) TO_DAYS(#{end_date})