CODEWITHSUNDEEP
mysql
sandeep
sandeep

Reputation: 375

mysql query select between dates shows wrong answer

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..

Upvotes: 0

Views: 2325

Answers (6)

Manohar
Manohar

Reputation: 31

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

Try this way.

Upvotes: 3

Bhaskar Bhatt
Bhaskar Bhatt

Reputation: 1467

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

It will run now because phpmyadmin has been upgraded

Upvotes: -1

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

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.

Upvotes: 4

Kerem
Kerem

Reputation: 11566

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.

Upvotes: 1

hjpotter92
hjpotter92

Reputation: 80639

  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.

Upvotes: 1

जलजनक
जलजनक

Reputation: 3071

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

Upvotes: 2

Related Questions