SQL Statement question.

[njdubois]

I have a database that uses the MM/DD/YYYY format for dates.

 

With out the option of changing the date format inside the database, whats the best way to pull data based on a range of dates?

 

What I have been doing is using or

 

SELECT * FROM TABLE WHERE

user_name='whatever' AND Session_Date='01/01/2012' OR

user_name='whatever' AND Session_Date='01/02/2012' OR

user_name='whatever' AND Session_Date='01/03/2012' OR

user_name='whatever' AND Session_Date='01/04/2012' OR

user_name='whatever' AND Session_Date='01/05/2012' OR

 

and so on.

 

The problem is, if we go much farther past a few weeks, it takes to long. We are trying to run it for the year, and that's obviously not working.

 

I have been meaning to change the data format, but the problem is that format dates a while back. We used it in an access database with vb years ago, and when we switched over to HTML/PHP/MYSQL I didn't know there was a better format. Now this date format is all over the site, and to change it is kind of a project. There is plans for a site revamp. As I said, its been a learning process for me. I have recently started testing a setup where key parts of each page that are used almost everywhere are put in an included php file instead. When that's done, a switch like this would be easy but until then I need a way to make this work.

 

Any suggestions?

 

As always, this site and community has been a huge help for me, many many thanks!

Nick

Edited January 4, 2013 by njdubois

[mikosiko]

have you tried some of the Mysql date functions?.... like date_format() per example?

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

[kicken]

If all the dates are in that same format, you can use the STR_TO_DATE function to convert them to a mysql date then use a range check.  That probably won't  help with your speed issue though as by doing the conversion at query time you prevent mysql from optimizing the query using indexes, as far as I am aware.

 

SELECT * FROM table WHERE user_name='whatever' and STR_TO_DATE(Session_Date, '%m/%d/%Y') BETWEEN '2012-1-1' AND '2012-1-5'