I have created a query which returns results between a date range, however the previous developer has shoot me on the foot by saving the date as a varchar(10) in MySQL and also in two formats (Y-m-d and d-m-Y).
My problem is that when I select results between a date from 01-June-2015 to 11-June-2015, it gave me 18 results for June 2015.
But when I select results between a date from 01-june-2014 to 11-June-2015, it gave me 13 results for June 2015 which must be 18 results, I think.
TASK: GET TOTAL OF NEW VISITORS IN A DATE RANGE BUT DATE IS NOT PRESENT IN VISITOR TABLE SO I TAKE HIS VERY FIRST VISIT DATE FROM VISIT TABLE That's why I use MIN() function
MySQL query is:
SELECT min(visits.id), t.id, t.first_name,
visits.date_check_in
FROM `visitor` `t`
JOIN `visit` `visits` ON visits.visitor = t.id
WHERE
((STR_TO_DATE(visits.date_check_in, '%d-%m-%Y') BETWEEN
STR_TO_DATE('11-06-2014', '%d-%m-%Y') AND STR_TO_DATE('11-06-2015',
'%d-%m-%Y'))
OR (STR_TO_DATE(visits.date_check_in, '%Y-%m-%d') BETWEEN
STR_TO_DATE('2014-06-11', '%Y-%m-%d') AND STR_TO_DATE('2015-06-11',
'%Y-%m-%d')) )
AND (t.is_deleted = 0)
AND (visits.is_deleted = 0)
AND (visits.visit_status != 2)
GROUP BY `t`.`id`
kindly help me what am I doing wrong. I have spent about 4 days by thinking and doing different things but got stuck.
I hope to hear from you quickly, thanks.
Copyright Notice：Content Author:「Owais」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/30771780/mysql-query-returns-wrong-result-count-between-a-date-range