I have a mysql and in which i set datetime column as nvarcha

Mark Tabor

I have mysql and in which i have date column set to varchar instead of date time and it store date in this format 07/17/2018 now i want o use between operator in my query but it does not work because the data type is varchar and between can only be applied on datetime column what to do now

function datevisereport()
{
global $tprefix,$con;
$data='';
$grandtotal=0;
$start_date=$_POST['start_date'];
$to_date =$_POST['end_date'];
$query="SELECT * FROM plan_voucher WHERE location=".$_SESSION['role']." and created_at between '".$start_date."' and '".$to_date."'";
$vouceher=mysqli_query($con,$query);
while($getvoucher=mysqli_fetch_array($vouceher))
{
$acamount=0;
$vdquery='select * from plan_vdetails where vid='.$getvoucher['id'].'';
$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.='.$bgcolor.'>
'.$getvoucher['created_at'].'
'.$getvoucher['voucherno'].'
"40%">'.$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.='
"border-bottom:1px solid balck;">'.$acamount.'.00
';
}
$data.='
"border-bottom:1px solid balck;" align="right">Grand Total:
"border-bottom:1px solid balck;">'.$grandtotal.'.00
';
return makepdf($data,$start_date,$to_date);
}

8 Replies
Know the answer? Post it — somebody with the same question will find it here.

Kesavan Krishnan8y ago
Check the below image
And

0
Mark Tabor8y ago
d donthu your query return zero below is the query which made by your format
SELECT *,CONVERT(created_at, DATE) as created_at FROM plan_voucher WHERE location=9 and DATE_FORMAT(STR_TO_DATE(created_at, '%c/%e/%Y'), '%Y-%m-%d') between '04/01/2018' and '08/21/2018'
0
Kesavan Krishnan8y ago
Hi Mark,

Check this

SELECT * FROM test_date where DATE_FORMAT(STR_TO_DATE(start_date,'%c/%e/%Y'),'%Y-%m-%d') between '1999-10-21' and '2010-01-19';
make sure date should be passed in between as Y-m-d format
0
Mark Tabor8y ago
Kesavan Krishnan SELECT * FROM plan_voucher WHERE location=1 and DATE_FORMAT(STR_TO_DATE(created_at,'%c/%e/%Y'),'%Y-%m-%d') between '07/16/2018' and '07/23/2018'
0
Mark Tabor8y ago
Kesavan Krishnan by that way it is showing 0 records even not on the correct month and year
0
D Donthu8y ago
we can try this
SELECT *,CONVERT(created_at, DATE) as CreatedA FROM plan_voucher
WHERE location={$_SESSION['role']} and DATE_FORMAT(STR_TO_DATE(CreatedA, '%c/%e/%Y'), '%Y-%m-%d') between '{$start_date}' and '{$to_date}

0
Kesavan Krishnan8y ago
Hi Mark,

check the below query

$query="SELECT * FROM plan_voucher WHERE location={$_SESSION['role']} and DATE_FORMAT(STR_TO_DATE(created_at, '%c/%e/%Y'), '%Y-%m-%d') between '{$start_date}' and '{$to_date}'";
0
D Donthu8y ago
Hi Mark ,

we can user below url per convert DateTime and apply the aliases and use condtion .
https://www.techonthenet.com/mysql/functions/convert.php
https://dba.stackexchange.com/questions/50391/why-does-mysql-allow-having-to-use-select-aliases
0