Jump to content

Date Search Help !!!


cheadirl

Recommended Posts

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.