mysql query select between dates shows wrong answer

Question

When I write a query in MySQL, e.g.

SELECT * FROM `customer` where date BETWEEN '01/20/2012' AND '01/31/2012'

it also shows the details of 2013 customers.

Can any one help me please..

Answer 1

You'll have to convert the VARCHAR to a DATE to compare it;

SELECT *
FROM `customer` 
WHERE STR_TO_DATE(`date`, '%m/%d/%Y')
BETWEEN '2012-01-20' AND '2012-01-31'

An SQLfiddle.

Note that this will miss any indexes you have on your column since every row needs to be converted before comparison, not good for performance.

Storing dates in a VARCHAR is generally seen as a bad idea, the sort order is non trivial and month/day can easily be confused if the system is used internationally. I'd recommend you convert the date column to a DATE datatype instead.

EDIT: This is one way of converting the column without losing your existing data;

# Add a new DATE column
ALTER TABLE `customer` ADD COLUMN tmpdate DATE;
# Transfer the data from the VARCHAR column to the DATE column
UPDATE `customer` SET tmpdate=STR_TO_DATE(`date`, '%m/%d/%Y');
# Drop the old VARCHAR column
ALTER TABLE `customer` DROP COLUMN `date`;
# Rename the new DATE column to `date`.
ALTER TABLE `customer` CHANGE `tmpdate` `date` DATE;

Of course you should never attempt an alter table command without doing a proper backup first.

An SQLfiddle demonstration of the conversion.

Note that PHP code relying on the date format may need some updating to work with DATE.

Answer 2

SELECT 
    *
FROM
    customer
WHERE
    STR_TO_DATE(dateField, '%m/%d/%Y') BETWEEN '01/20/2012' AND '01/31/2012';

Try this way.

Answer 3

MySQL Date & Time Functions

SELECT 
    *
FROM
    customer
WHERE
    STR_TO_DATE(dateField, '%m/%d/%Y') BETWEEN STR_TO_DATE('01/20/2012', '%m/%d/%Y') AND STR_TO_DATE('01/31/2012', '%m/%d/%Y');

Answer 4

Assuming date field type is DATE;

SELECT * FROM `customer` WHERE `date` BETWEEN '2012-01-20' AND '2012-01-31'

From manual;

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format.

Ref: http://dev.mysql.com/doc/refman/5.5/en/datetime.html

UPDATE

However, it's bad idea to store dates as varchar, maybe the query could be like:

SELECT * FROM `customer` WHERE `date` REGEXP '^2012-01-(2[0-9]|[0-3][0-9])$'

Ref: http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp

But if it's possible, you need to exactly change your field type from VARCHAR to DATE.

Answer 5

1. Change the datatype of your date to DATETIME or simply, DATE like this:

   UPDATE `customer`
       SET `date` = STR_TO_DATE( `date`, '%m/%d/%Y' );
   

2. After the update, use ALTER like this:

   ALTER TABLE `customer`
       CHANGE COLUMN `date` `date` DATE NOT NULL;
   

3. After that, use this kind of select query:

   SELECT *
   FROM `customer`
   WHERE `date` BETWEEN '2012-01-20' AND '2012-01-31';
   

---

The reason behind why is changing the datatype good, is explained here: When to use VARCHAR and DATE/DATETIME

Read other date and time related datatypes here: The DATE, DATETIME, and TIMESTAMP Types.

Answer 6

SELECT * FROM `customer` where date BETWEEN '2012/01/20' AND '2012/31/01'

It will run now because phpmyadmin has been upgraded