Help With Mysql Select Between/where

[whelpton]

Hey guys,

 

First of all, thanks for reading my post, its much appreciated.

 

I've been roped into trying to develop a system to work with a large CSV file, which I believe should be ported into a mysql db to start off with then processed via php.

 

The only issue comes into searching through the DB and finding a specific date range, I am trying to use the mysql between code such as this:

 

SELECT * FROM sample WHERE `order_date` >= "01/01/2010" and `order_date` < "30/12/2010"

 

But I still get order_date's that are outside of that date range.

 

Any ideas?

[Pikachu2000]

Dates should be in YYYY-MM-DD format in the DB.

[whelpton]

Thanks for the reply PIkachu2000,

 

Is there a way around this, as the data is imported from a 100/200mb csv file that would be a pain in the arse to manually change and I imagine a pain in my processor's arse to change through code

[Pikachu2000]

Re-import it using MySQL's STR_TO_DATE() function to get the date in the right format,

add a new field and UPDATE the date into the new field with STR_TO_DATE()in the proper format and use that field for the comparisons,

or use STR_TO_DATE() on the values in your comparisons.

[PFMaBiSmAd]

I imagine a pain in my processor's arse to change through code

 

It would be a total of one UPDATE query, without a WHERE clause, to convert the existing value and set a new column to the corresponding YYYY-MM-DD value, all at once.

[whelpton]

Thanks gents, much appreciated =)