Date Search Help !!!

[cheadirl]

Hi ,

I've been trying to get this sorted for ages and have searched loads of forums and so on ... no joy !
Basically I have a DB setup with a timestamp filed and date filed

timestamp = time();
date = date('d-m-Y' , time());

This is working fine and inputting the timestamp and also the date into my DB, I have a seach function on the date where the user selects a date from a calendar ie 05-05-2006 and that search the date colum, then I have the ability to seach back 7 days using the time() - 60*60 etc ....

My problem is searching between dates !!! When the user inputs 01-04-2006 to 01-05-2006 and using the sql command WHERE date >= $date1 AND date <= $date2 it doesnt work, and I have no idea how to make it work - i've tried converting the 01-04-2006 to a timestamp but no luck either and it tries to find the exact match even using LIKE its not getting it.

Can anyone help me with this - I hope what I'm saying makes sence lol

Thanks
Wayne

[obsidian]

hey, wayne, the problem is that in SQL, a DATE or DATETIME field (which you need in order to run a search between dates) is formatted as "YYYY-MM-DD", not the other way around. now, there may be a workaround for you, but it also may cause some unnecessary overhead on your server. try something like this out and see what happens:

[code]
$date1 = "04-15-2006";
$date2 = "05-05-2006";

// first, let's change the dates to a readable format for MySQL:
list($month1, $day1, $year1) = explode("-", $date1);
list($month2, $day2, $year2) = explode("-", $date2);
$date1 = "$year1-$month1-$day1";
$date2 = "$year2-$month2-$day2";

// now, let's run the comparison with taking into account that we
// have to change the actual column values into readable dates, too
$sql = "SELECT * FROM tableName WHERE DATE(CONCAT_WS("-", SUBSTRING(dateColumn, 7), SUBSTRING(dateColumn, 1, 2), SUBSTRING(dateColumn, 4, 2))) BETWEEN '$date1' AND '$date2'";

$res = mysql_query($sql);
[/code]

like i said, i'm not positive this will solve it for you as you have it now, but it's worth a try. i would recommend changing the column to a DATE type and formatting your insert to match the SQL DATE format.

[craygo]

Another thing to go with obsidian's post, If you are getting the current date and/or time from php and inserting it into mysql, there in no need to have the field a timestamp. Just make it a date and format your date as YYY-MM-DD

Ray