CODEWITHSUNDEEP
phpmysqldateselect
Abilash Amarasekaran
Abilash Amarasekaran

Reputation: 838

MYSQL Select between date breaking on year

I have created a simple form which users submit. Everything works great but I recently found that the 

SELECT * FROM `Forms` WHERE `Date` BETWEEN '{$startDate}' AND '{$endDate}'

The column Date is type TEXT. I needed it to be text cause I thought it would be easier to display everything in MM/DD/YY format. Now I dont want to risk changing the data type since the form is working fine.

Example of Date column

01-03-2013
01-04-2013
07-25-2012
08-01-2012
08-01-2012
08-01-2012
08-01-2012

Ex of working Query

SELECT * FROM `Forms` Where `Date` Between '01-08-2012' and '12-12-2012'

Ex of not working Query

SELECT * FROM `Forms` Where `Date` Between '01-08-2012' and '01-04-2013'

Any reason why it would break if the year changes? How can I get it to work even if the year changes.

Upvotes: 3

Views: 4449

Answers (5)

echo_Me
echo_Me

Reputation: 37233

you can do it like that

SELECT * FROM `Forms` 
WHERE str_to_date(`Date`, '%d-%m-%Y') BETWEEN '2012-01-30' AND '2013-09-29'

DEMO HERE

EDIT :

if you want fix your table here how you do

  1. Add a new column of the appropriate DATE data type:

    ALTER TABLE `Forms` ADD `new_date` DATE AFTER `Date`;

  2. Use MySQL's STR_TO_DATE() function to populate that new column with the dates held in the old column:

    UPDATE `Forms` SET `new_date` = STR_TO_DATE(`Date`, '%d-%m-%Y');

  3. Drop the old column (and, if so desired, rename the new one in its place):

    ALTER TABLE `Forms` DROP `Date`, CHANGE `new_date` `Date` DATE;

  4. Change your application to use this new column.

Upvotes: 5

Tarika
Tarika

Reputation: 246

try this:

select * from "forms" where Date('column_name') between '' and ''

Upvotes: 0

Marc
Marc

Reputation: 514

If you are worried about it breaking then export the database then make the change. You won't get what you expect because it is a TEXT field not a date field. MySQL is sorting per character. As in it is looking at the first character then the next then the next.

Like navnav said it won't break but make a backup just encase.

As for displaying only the date you can explode() on a space to get only the date:

<?php

list($date, $time) = explode(" ", $datetime, 2);
echo $date;

?>

Upvotes: 0

Halcyon
Halcyon

Reputation: 57728

Because your column is a TEXT column MySQL will use an alphabetic compare.

01-08 comes before 01-04 so it's actually the month part already that breaks.

To fix this, either convert the column to a DATE type or reverse the order of the date to YYYY-MM-DD, in both cases the BETWEEN should function correctly.

Upvotes: 4

Sashi Kant
Sashi Kant

Reputation: 13465

Try this::

SELECT * FROM `Forms` Where str_to_date(`Date`, '%d/%m/%Y') Between '01-08-2012' and '12-12-2012'

Upvotes: 2

Related Questions