0
721
ViewsHow to fix MYSQL between date not giving accurate results?
SELECT
IF
(
SUM( t.debit )>= SUM( t.credit ),
SUM( t.debit ) - SUM( t.credit ),
SUM( t.credit ) - SUM( t.debit )) AS Balance,
IF(SUM( t.debit )>= SUM( t.credit ) ,"Profit", "Loss") AS status
FROM
transactions AS t
WHERE
t.userId = 1 AND t.dateIssued BETWEEN '2021-07-09' AND '2021-07-10';
I expected to give 6000 as the output because the between should be date inclusive
·
Santiago Trujillo
1 answers
Answer question0
dateIssued is not a Date, it is a DateTime/timestamp.
So, when you compare its value 2021-07-10 13:11:12 to dates like '2021-07-09' and '2021-07-10' the comparison is interpreted as:
'2021-07-10 13:11:12' BETWEEN '2021-07-09 00:00:00' AND '2021-07-10 00:00:00'
which returns false.
Convert dateIssued to Date with the function Date() and then compare:
WHERE t.userId = 1 AND DATE(t.dateIssued) BETWEEN '2021-07-09' AND '2021-07-10';
For better performance you should consider writing the conditions without BETWEEN and the function Date():
WHERE t.userId = 1
AND t.dateIssued >= '2021-07-09'
AND t.dateIssued < '2021-07-11';
·
Santiago Trujillo
Report
Answer question