在mysql日期时间列中搜索日期

Question

I have a table in MYSQL database with a column db_date with type is datetime ex:2016-10-20 01:05:00

I use a PHP code to search different thing the date is one of them for that i use a date picker have the date like this 10/21/2016 m/d/Y

for that i use this to transform this form to the form in database

STR_TO_DATE('".$date."','%m/%d/%Y') 

The Problem is that when i use db_date= STR_TO_DATE('".$date."','%m/%d/%Y') it give only the date that have hour and minutes and second 00:00:00

but i want all the date ex: if i choose 10/20/2016 i want all date with different time in database be selected

i tried to use the date function in mysql but i didn't have result date(STR_TO_DATE('".$date."','%m/%d/%Y'))

And also i tried to use the DATE_FORMAT(STR_TO_DATE('".$date."','%m/%d/%Y')) and i didn't have a result

the query will be like this $query=SELECT * FROM tbl_staff {$sql}

and this is the code

$q = array();    
    $sql = "";
    if(isset($_POST['txt_date']) && !empty($_POST['txt_date'])){  
    $date = mysqli_real_escape_string($conn,$_POST['txt_date']); 
    $q[] = " db_date=STR_TO_DATE('".$date."','%m/%d/%Y') and db_status!='Done' AND db_status!='Cancelled'";      
    } 

How can i solve this problem ?!

Answer 1

First of all you can use format in datapicker to customise the date :

$('#date').datepicker({format: 'yyyy-mm-dd'});

And for your sql query do (use date() for db_date)

$q[] = " DATE(db_date)= $date and db_status!='Done' AND db_status!='Cancelled'";