Different result between Query and stored procedure-CodePudding

So here is my query

SELECT count(distinct (id_customer)) as customer
from `orders`
WHERE order_date >= '2022-06-01 08:30%' AND order_date <= '2022-06-06 08:30%';

the result is 2069.

Then I create this stored procedure

CREATE PROCEDURE customer_count(IN start_date DATE, IN end_date DATE)
BEGIN
    SELECT count(distinct (id_customer)) as customer
    from `orders`
    WHERE order_date >= start_date AND order_date <= end_date;
END;

and then call it using

CALL customer_count('2022-06-01 08:30%', '2022-06-06 08:30%');

it return 2126

I have done some googling but still no luck.

CodePudding user response：

You had used the DATE datatype in procedure parameters so then it compares only date value.

please change the DATE datatype to DATETIME, You will get expected output and don't use % (wild character search) in date columns.

Better use following query:

SELECT count(distinct (id_customer)) as customer
from `orders`
WHERE order_date >= '2022-06-01 08:30:00' AND order_date <= '2022-06-06 08:30:59';

Change the procedure as follows:

CREATE PROCEDURE customer_count(IN start_date DATETIME, IN end_date DATETIME)
BEGIN
SELECT count(distinct (id_customer)) as customer
from `orders`
WHERE order_date >= start_date AND order_date <= end_date;
END;