MySQL BETWEEN doesn't work as intended

Question

I have a table with data for customers. Each entry has a specified customer and a start date and an end date. I want to display specific customer data that has startdate between two variables, i.e. i ask the user on the frontend to choose 1. a customer and 2. startdate to start somewhere between x and y. So that the displayed data doesn't take the end date into account because it's more important to see what data is starting in that time range and not what data is ending.
I used to do this like this:

WHERE c2c.CUSTOMER LIKE '%". $customers ."%' AND (MASTER.MASTER_SCHEDULED_START_DATE >= '". $datumanf . "' AND MASTER.MASTER_SCHEDULED_START_DATE <= '". $datumend . "')

This looked like it worked! Until I noticed that it only displays data where the start date and the end date are on the same day.

After researching on Stackoverflow I tried MySQL BETWEEN

WHERE c2c.CUSTOMER LIKE '%". $customers ."%' AND (MASTER.MASTER_SCHEDULED_START_DATE BETWEEN '". $datumanf . "' AND '". $datumend . "')

But this still somehow takes the end date into account, for it shows the exact same results as before: Only those where the startdate and the enddate are on the same day and between that defined range. I don't understand this because I don't use the end date at all in this query.

I know, but I only operate from within a secured virtual network. Please disregard this issue — sardine
– sardine, Commented Feb 4, 2016 at 8:38
Did you check your data maybe startdate and enddate is always the same for each record? — Dr. Stitch
– Dr. Stitch, Commented Feb 4, 2016 at 8:40
The firs version should work but you have to define the variables as a date type. There seems to compare strings and i think that is the reason to return only the data that have start and end date on the same day — Corina Gheorghe
– Corina Gheorghe, Commented Feb 4, 2016 at 8:41

SUDARSHAN BHALERAO · Accepted Answer · 2016-02-04 08:51:49Z

This might will help you out..

WHERE c2c.CUSTOMER LIKE '%". $customers ."%' AND (MASTER.MASTER_SCHEDULED_START_DATE) BETWEEN ('". $datumanf . "' AND '". $datumend . "')

sardine · Accepted Answer · 2016-02-04 08:55:16Z

The answer was said first @corinagheorge in the comment section. I had to set datatype first.

Collectives™ on Stack Overflow

MySQL BETWEEN doesn't work as intended

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related