Mysql LEFT function

Hai folks,

i am struggling to use the LEFT function in the below scenario :

sms_logs table :

2012/08/11 11:03:27 AM
2012/08/13 11:03:27 AM
2012/08/13 11:03:27 AM
2012/08/14 11:03:27 AM
2012/08/15 11:03:27 AM

i want to retrieve all the rows recorded on 2012/08/13

so i am using the below code

$query = "SELECT * from sms_logs WHERE LEFT(date,10)='$dt'";

The above query returns 0. whats wrong?

edited :

also tried below and failed. :rolleyes:

$query = "SELECT LEFT(date,10) from sms_logs WHERE date='$dt'";

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'";

works fine for me.

Sorry for the trouble :slight_smile:

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

Thanks for the valuable comments

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

Thanks r937.
i understand. btw, my date and time column is VARCHAR 255.

o rly?

in that case, disregard everything i said in this thread until you change it to DATETIME


really sorry for not mentioning that :slight_smile:

no problem, the data bese is just built. i can change the filed to DATETIME and follow your instructions even for a better performance.