MySQL Date Search MM/DD/YYYY to MM/DD/YYYY

Question

I have a table in MySQL That looks like the following:

date        |storenum   |views 
-------------------------------
08/21/2009  |42         |3  
-------------------------------
08/22/2009  |43         |1    
-------------------------------
08/21/2009  |43         |4  
-------------------------------
08/22/2009  |42         |22
-------------------------------

I know that this is an unconventional date format, but I am using it as opposed to YYYY-MM-DD for ease of use. Yet i have to search this database to get all "View Records" Between two dates. In general it works but when I search from a date such as 01/01/2009 to 01/01/2010 I get no results.

Does anyone have any suggestions? Constructive criticism is welcome. Thanks!

Answer 1

In all honesty, change the way you store dates. But for working with what you have now, you could look into using STR_TO_DATE() to convert these to dates that MySQL can work with.

Answer 2

Try the search with format "yyyyMMdd" MySql will recognize it for sure.

Answer 3

You mention ease of use - I think the case you are trying to resolve now is an example of "not so easy to use" :).

I would stick to the default format of dates in MySQL

yyyy-mm-dd h:i:s //php's date formatting

and use DATE fields. This will solve the searching/inserting of dates problem. Though you need to change some code to fix displaying the dates.

Answer 4

what is your date field type? for a correct use it should be date or datetime, and seeing what you have it looks like you store you dates in a varchar. am I right?

This also have the advantage you can add indexes, and then search in ranges trough mysql

my suggestion is get whatever the user enter. and then convert it to a mysql date format for searching

something like:

<?php

$mysql_time = date('Y-m-d H:m:s' strotime('05/11/2008'));

$query = sprintf("SELECT * FROM sometable < '%s'", $mysql_time);

?>