MySql日期查询出错 - MySql date between query went wrong

MySql date between query went wrong

本文关键字：出错查询日期 MySql | 更新日期: 2023-09-27

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'

在此查询中，我想从表sales_inv中获得总数，其中销售发票中的order_num和cnf_order中的order_number应该相等。

added_on是一个时间戳，它应该在$month1和$month2之间

$month1和$month2是按月存储的变量，时间戳格式

这个查询不工作，我做错了什么?

你试过了吗

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')

编辑:

使用日期函数

   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'))

尝试:

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')