CODEWITHSUNDEEP
mysqlsql
Jemuel Elimanco
Jemuel Elimanco

Reputation: 556

mysql between datetime shows wrong record

I have this query:

SELECT id
     , order_date
     , seat_number
     , cashier_fk
     , branch_fk
     , waiter_fk
     , void
     , total_amount
     , customer_name
     , payment
     , notes
     , down_payment
     , received_date
     , void_reason
     , discount
     , discount_percentage
     , printed
     , done
     , vat
     , service_charge
  FROM order_tbl
 WHERE received_date between "2018-03-15" AND "2018-03-18"

but it shows My table has this record:

id-------order_date
1-------2018-03-09 09:09:25
2-------2018-03-13 18:29:16
3-------2018-03-13 20:00:49
4-------2018-03-13 20:01:46
5-------2018-03-13 20:05:48
6-------2018-03-13 20:06:34
7-------2018-03-13 20:07:15
9-------2018-03-16 19:06:23
10-------2018-03-16 20:22:26

But it shows only

id-------order_date
5-------2018-03-13 20:05:48
6-------2018-03-13 20:06:34
7-------2018-03-13 20:07:15
9-------2018-03-16 19:06:23
10-------2018-03-16 20:22:26

What is wrong with my query?

Upvotes: 2

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270693

Do not use between with dates or date/times. The time component throws everything off.

Instead, express the logic as:

WHERE received_date >= '2018-03-15' AND
      received_date < '2018-03-19'

Note the inequality at the end of the range. This ensures that you get everything from that date.

Aaron Bertrand has a really good blog post on the subject, What do BETWEEN and the devil have in common?.

Upvotes: 4

Related Questions