date range problem

[rmelino]

Hi All,

 

I am trying to do a query between a date range and having problems.  I know the main issue is that i stored my date values in my db improperly (i used varchar, doh!).  Anyhow, i'm trying to see if there is a workaround instead of having to reformat my whole db for every time entry.

 

Right now, my time entries look like this:  08/19/11 16:13:05

 

I am trying to write a simple query that allows me to pull results from within a date range where i can change  month, day or year.  So, something like this:

 

$query = "SELECT * FROM blah WHERE `dog` LIKE '%lab%' AND request_date BETWEEN ' 08/10/11 16:13:05' AND  08/19/11 16:13:05' "; 

or, changing the days...

$query = "SELECT * FROM blah WHERE `dog` LIKE '%lab%' AND request_date BETWEEN ' 08/15/11 16:13:05' AND  08/19/11 16:13:05' "; 

or, changing the months...

$query = "SELECT * FROM blah WHERE `dog` LIKE '%lab%' AND request_date BETWEEN ' 07/15/11 16:13:05' AND  08/19/11 16:13:05' "; 

or, changing the years...

$query = "SELECT * FROM blah WHERE `dog` LIKE '%lab%' AND request_date BETWEEN ' 08/15/10 16:13:05' AND  08/19/11 16:13:05' "; 

 

using the queries above don't seem to pull the accurate results.

 

Could someone help?

 

Thanks in advance

[dadamssg87]

pretty positive you're gonna have to change your database structure to timestamp or datetime. You can't use between or date sql functions on data that's not in a date format.

 

Why don't you just create a new table with datetime instead of varchar and write a script that pulls all the rows from the old table and insert them into the new table with the right format? I think that would be WAY better in the long run.

[Pikachu2000]

You could add a DATETIME field to the table, and populate it with an UPDATE query using MySQL's STR_TO_DATE() function, then once you've verified the query was successful you can delete (or rename) the existing field and rename the new field.

[gizmola]

The same functions that you would use to convert (note Pika2k's suggesting of STR_TO_DATE for example) can be used to do the queries you're doing above.  They potentially will be quite slow because every single row will have to be examined to see if it fits the criteria, as mysql can not utilize an index in a case like this. 

 

Yet another reason to bite the bullet and convert, but it needed to be said that STR_TO_DATE can facilitate those queries even though it requires table scanning.

[rmelino]

Thank you all for the helpful replies.  Any chance you could provide a code example of how i would convert my existing entries to yyyy/mm/dd h/m/s using STR_TO_DATE()?

 

Thanks again for your help!

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date