Sorry folks,
i messed up a bit.
i have shown you $dt='$dt=‘2012/08/13’;
actually i am using $dt=date(‘Y-m-d’);
the problem really was i was using - insted of / in the date() function.
so now
$query = "SELECT * from sms_logs WHERE LEFT(date,10)='$dt'";
then you are lucky that mysql is such a forgiving, lenient database
dates are not strings – please do not use string functions on them, for two reasons
first, if you do use a string function (like LEFT), you force an implicit conversion of the column value from datetime to string
this leaves open the possibility that the conversion to string does not meet the format that you’re expecting, and your code will fail
but second, and far more important, when you apply a function to a column value like that, the database has no choice but to do a table scan, which is very inefficient, even if that column has an index on it for optimization
the “best practice” for what you’re trying to do is an open-ended range test –
WHERE `date` >= '2012-08-13'
AND `date` < '2012-08-14'
please let me know if you don’t understand what that does
WHERE date >= ‘2012-08-13’
AND date < ‘2012-08-14’
Small doubt on ur comment.
in actual coding i use $dt=date(‘Y/m/d’); not really 2012-08-13. that in mind…
the above example you give looks like again a string comparison for me? not require to convert the dates to time stamp or anything on both sides (to the =) ?
no problem, mysql will recognize that easily… also ‘2012#08#13’ and ‘2012@08@13’ et cetera
no, it’s not a string comparison
mysql recognized that your date column is a datetime, and so it converts ‘2012-08-13’ (which, you are correct, is a string) to datetime format, and then it does a datetime comparison
by the way, date is a poor choice for a column name, because DATE is a reserved word, and you will often get an error message if you forget to use those pesky backticks
