Mysql query return wrong result set

Question

I have mysql database in which i have a datetime column i am saving date in format date("Y-m-d") but my below query "SELECT aid,glid FROM plan_vdetails WHERE location=16 and vdate2 between '2018-09-05' and '2018-09-05' group by aid,glid" Return result of Month=05 as well no matter here i am giving it year 2018, month =09 and day = 05 , can some body help me

Amit Kumar · Answer

Hi, Dear pass your date format according to your database from jquery request.

Mark Tabor · Answer

Thanks for Packiaraj Santhiyagu actually i have a table in which i have two columns previously there data type was varchar but i change it to date now , Now when my below query executes it shows the records of 2018-09-05 the format is year-month-day , but the query return the rows of 2018-05-09 also i do not know why i can share my project with you if you can give me email to connect with my machine below is my query. and table snapshot. function datevisereport() { global $tprefix,$con; $data=''; $grandtotal=0; $start_date= date("Y-m-d",strtotime($_POST['start_date'])); $to_date = date("Y-m-d",strtotime($_POST['end_date'])); $query="SELECT aid,glid FROM plan_vdetails WHERE location=".$_SESSION['role']." and vdate2 BETWEEN '".$start_date."' and '".$to_date."' group by aid,glid"; echo $query; echo(" "); $vouceher=mysqli_query($con,$query); while($getvoucher=mysqli_fetch_array($vouceher)) { $acamount=0; $vdquery='select * from plan_vdetails where location='.$_SESSION['role'].' and aid='.$getvoucher['aid'].' and glid='.$getvoucher['glid'].' and vdate2 between "'.$start_date.'" and "'.$to_date.'"'; echo($vdquery); echo(" "); $vdetails=mysqli_query($con,$vdquery); while($getvdetails=mysqli_fetch_assoc($vdetails)) { if($getvdetails['transtype']=='DR') { $amountdr=$getvdetails['amount']; $amountdcr=0; } else { $amountdcr=$getvdetails['amount']; $amountdr=0; } if($getvdetails['isrectify']==1) { $bgcolor='bgcolor="yellow"'; } else { $bgcolor=''; } $data.=' '.$getvdetails['vdate2'].' '.getSingleValue('voucher','voucherno',' where id='.$getvdetails['vid']).' '.$getvdetails['glid'].'-'.getSingleValue('accounts','account_no',' where id='.$getvdetails['aid']).' '.$getvdetails['description'].'_'.getSingleValue('employees','fullname',' where id='.$getvdetails['claim_person']).' '.$amountdr.' '.$amountdcr.' '.$getvdetails['rbalance'].' '; $acamount=$acamount+$amountdr; } $grandtotal=$grandtotal+$acamount; $data.=' '.$acamount.'.00 '; } $data.=' Grand Total: '.$grandtotal.'.00 '; return makepdf($data,$start_date,$to_date); } "my query returns below SELECT aid,glid FROM plan_vdetails WHERE location=16 and vdate2 BETWEEN '2018-09-05' and '2018-09-05' group by aid,glid select * from plan_vdetails where location=16 and aid=0 and glid=160000 and vdate2 between "2018-09-05" and "2018-09-05" select * from plan_vdetails where location=16 and aid=155 and glid=600050 and vdate2 between "2018-09-05" and "2018-09-05" select * from plan_vdetails where location=16 and aid=155 and glid=610060 and vdate2 between "2018-09-05" and "2018-09-05" select * from plan_vdetails where location=16 and aid=155 and glid=610070 and vdate2 between "2018-09-05" and "2018-09-05" select * from plan_vdetails where location=16 and aid=155 and glid=631000 and vdate2 between "2018-09-05" and "2018-09-05" select * from plan_vdetails where location=16 and aid=155 and glid=666070 and vdate2 between "2018-09-05" and "2018-09-05" select * from plan_vdetails where location=16 and aid=158 and glid=601030 and vdate2 between "2018-09-05" and "2018-09-05" select * from plan_vdetails where location=16 and aid=158 and glid=601050 and vdate2 between "2018-09-05" and "2018-09-05" select * from plan_vdetails where location=16 and aid=159 and glid=600050 and vdate2 between "2018-09-05" and "2018-09-05" select * from plan_vdetails where location=16 and aid=162 and glid=666070 and vdate2 between "2018-09-05" and "2018-09-05" select * from plan_vdetails where location=16 and aid=163 and glid=667050 and vdate2 between "2018-09-05" and "2018-09-05" select * from plan_vdetails where location=16 and aid=271 and glid=666050 and vdate2 between "2018-09-05" and "2018-09-05" My table structure is these are the records of fifth month not of 9th month

Packiaraj Santhiyagu · Answer

Hi Mark, Can you explain your question little bit clearly with scripts? It would be better to help.