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

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