Searching for all date formats

[middleman666]

Hello all,

I'm trying to make a search query that will search a DATETIME field in a database table and return true if there is one or more exact matches.

I'm wondering if theres a simple way to ask if the date is equal to the date the user searches for, even if the format the user inputs doesn't match the default DATETIME format.

So the user could input: 18-07-2007 or 18/07/2007 or 07/18/2007 or 18th July 2007 and so on... and it would return true for the date from the table: 2007-07-18 00:00:00

I know I could use DATE_FORMAT() to think up every possible way the user could input the date and I can try and match it against each of them, but I was wondering if theres a simpler way?

Many Thanks.

[Piranha]

It is not possible to do this. What if the user inputs 10/12/2007? Or if someone inputs 09/10/11? As long as there is the possibility that one input can mean more than one thing you simply can't do what you want.

Instead tell the user how to input the date, it will work.

[middleman666]

Cheers for the reply, I see what you mean.

I decided (just to test it) to use the format MM-DD-YYYY as the format for the user to use when searching for an exact date.

I have this code:

$SDresult=mysql_query("SELECT id, Date, DATE_FORMAT(Date, '%d-%m-%Y') AS searchdate FROM TESTTABLE WHERE searchdate = '$var'"); 
$SDrow=mysql_fetch_array($SDresult); 
$SDnumrows=mysql_num_rows($SDresult);
if ($SDnumrows != 0) {
  echo 'Exact Date Found: '.$SDrow['searchdate'].'<br />';
}

But I'm getting errors that say mysql_fetch_array(): and mysql_num_rows(): supplied argument is not a valid MySQL result .

do you know what I'm doing wrong?

Thanks.

[NogDog]

Validate your query (that error indicates that MySQL was not able to process your query):

$sql = "SELECT id, Date, DATE_FORMAT(Date, '%d-%m-%Y') AS searchdate FROM TESTTABLE WHERE searchdate = '$var'";
$SDresult = mysql_query($sql) or die("Query failed ($sql): " . mysql_error());

[middleman666]

Cheers, I get:
Query failed (): Unknown column 'searchdate' in 'where clause'

Can you not use the name you set (AS searchdate) in the WHERE clause?

EDIT:

ok, as it doesn't look like you can do that, I'm trying this instead:

$yyyy = substr($var,6,4);
$mm = substr($var,3,2);
$dd = substr($var,0,2);
$date = $yyyy.'-'.$mm.'-'.$dd;
$SDresult=mysql_query("SELECT id, Date FROM TABLENAME WHERE Date = \"$date%\""); 
$SDrow=mysql_fetch_array($SDresult) or die("Query failed: " . mysql_error());
$SDnumrows=mysql_num_rows($SDresult);
if ($SDnumrows != 0) {
  echo 'Exact Date Found: '.$SDrow['Date'].'<br />';
}

I'm trying to make the users date dd-mm-yyyy match a date in the table (yyyy-mm-dd 00:00:00), but as I don't want the time, i'm trying to use \"$date%\" to say that the date im searching for, starts with the users date.

It just returns 'Query failed:' with nothing else.

Any idea where I'm going wrong?

EDIT2:

I changed:

$SDresult=mysql_query("SELECT id, Date FROM TABLENAME WHERE Date = \"$date%\"");

To:

$SDresult=mysql_query("SELECT id, Date FROM TABLENAME WHERE Date LIKE \"$date%\"");

and now I think it's working.

[bradgrafelman]

Or just use the MySQL DATE() function.