CODEWITHSUNDEEP
mysqldatetime
Bilbit Bolson
Bilbit Bolson

Reputation: 172

MySql query BETWEEN results are what I expected

I am filtering the data by dates, using a BETWEEN query but if I filter, for example, from 2022-09-20 to 2022-09-22, the data that is recovered are only from 2022-09-20 to 2022-09-21, is skipping the last day.

This is my query:

SELECT Delivery.Delivery, Order.Order, Order.Remittance, Delivery.Date 
from Delivery e
INNER JOIN Order p 
on Delivery.Delivery = Order.Delivery 
WHERE Delivery.Date BETWEEN '2022-09-20' AND '2022-09-23'
GROUP BY Delivery.Delivery, Order.Order, Order.Remit 
ORDER BY Delivery.date ASC

Result:

DELIVERY ORDER REMIT DATE
1111     1111  1111  2022-09-20
2222     2222  2222  2022-09-21
3333     3333  3333  2022-09-22

EXPECTED:

DELIVERY ORDER REMIT DATE
1111     1111  1111  2022-09-20
2222     2222  2222  2022-09-21
3333     3333  3333  2022-09-22
4444     4444  4444  2022-09-23

What I'm doing wrong?

Upvotes: 1

Views: 59

Answers (1)

Rajat
Rajat

Reputation: 5803

When you use between for dates, SQL reads them as yyyy-mm-dd 00:00:00. You either need to include the time part or change your condition to

e.Date >='2022-09-20' and e.Date < '2022-09-24'

Upvotes: 1

Related Questions