mysql - 如何使用存储过程从 01-04-2014 到当前日期之间的 mysql 数据库中获取数据

Question

数据库表包含日期时间类型为发票时间的字段，我必须获取从给定日期到当前日期的数据。我按如下方式创建了程序，但它不起作用。我指定的开始日期为 01-04-2014，但此日期之前的日期也被提取到表中。我添加了选择指定日期之间数据的条件，但它不起作用。有人帮我解决这个问题。

CREATE DEFINER=`root`@`localhost` PROCEDURE `list_invoice`(invoice_number varchar(30),branch_code varchar(15))

begin    

select `invoice number`,date_format(`invoice date`,'%d-%m-%Y') `invoice date`,`customer name`,sum(b.amount) `total amount`,sum(round(b.amount * a.`tax percentage`,2)) 
`tax amount` from invoices a
inner join `challan master` b on b.`challan number` = a.`challan number`
inner join `customer master` c on c.`customer id` = b.`customer id`
where (((((locate(invoice_number,`invoice number`) > 0 or locate(invoice_number,`customer name`) > 0) and invoice_number <> '')
               or
      (invoice_number = ''))and(`invoice number` like concat('%',branch_code,'%'))) AND (date_format(`invoice date`,'%d-%m-%Y') BETWEEN '01-04-2014' and date_format(CURDATE(),'%d-%m-%Y')))
group by `invoice number`,`customer name`
order by date_format(`invoice date`,'%Y-%m-%d')desc, 1 desc;

end

Answer 1

将此添加到 where 子句中的查询:

where ((((locate(invoice_number,`invoice number`) > 0 or locate(invoice_number,`customer name`) > 0) and invoice_number <> '')
               or
      (invoice_number = ''))and(`invoice number` like concat('%',branch_code,'%')) and `invoice date` between date_format(str_to_date('01-04-2014','%d-%m-%Y'),'%Y-%m-%d') and date_format(CURDATE(),'%Y-%m-%d'))
group by `invoice number`,`customer name`
order by date_format(`invoice date`,'%Y-%m-%d')desc, 1 desc;