MySql date between query went wrong

select si.total 
from sales_inv si,c nf_order co 
where si.order_num=co.order_no 
and co.cnf_no='CNF001' 
and co.added_on 
between 开发者_开发知识库'$month1' and '$month2'

Here in this query i want to get the total from the table sales_inv where the order_num in sales invoice and order_number in cnf_order should be equal.

added_on is a timestamp which should be between $month1 and $month2

$month1 and $month2 are variables stored with months in timestamp format

This query is not working, What am I doing wrong?

---

did you try this

select si.total 
from sales_inv si,c nf_order co 
where si.order_num=co.order_no 
and co.cnf_no='CNF001' 
and ( co.added_on 
between '$month1' and '$month2')

Edit :-

use date function

   select si.total 
    from sales_inv si,c nf_order co 
    where si.order_num=co.order_no 
    and co.cnf_no='CNF001' 
    and ( DATE(co.added_on) 
    between DATE('$month1') and DATE('$month2'))

---

Try:

select si.total 
from sales_inv si,c nf_order co 
where si.order_num=co.order_no 
and co.cnf_no='CNF001' 
and co.added_on 
between STR_TO_DATE('$month1', '%Y-%m-%d') and STR_TO_DATE('$month2', '%Y-%m-%d')