mhmd mhmd - 1 month ago 8
MySQL Question

Search date in mysql date time column

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

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