How to fix MYSQL between date not giving accurate results?

enter image description here

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

enter image description here

Answer

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';