How to select data between same two dates in mysql-CodePudding

Im trying to select data between two of the same date, as the user can select Date From and Date to

SELECT *
    FROM order
    WHERE order_date >= '2022-12-01'
    AND order_date <= '2022-12-01'

This doesnt work

CodePudding user response：

If your order_date column is that of a date/time (not just a date), then have the where clause

WHERE
       Order_Date >= '2022-12-01'
   AND Order_Date < '2022-12-02'

This way it gets all orders on 12-01 up to 11:59:59pm (hence LESS than December 2)

CodePudding user response：

If the user can enter the 2 dates as the same date you could do

SELECT *
FROM order
WHERE order_date BETWEEN '2022-12-01 00:00:00' AND '2022-12-01 23:59:59'

CodePudding user response：

Your problem may be concerning the table name: order is a DBMS keyword, you shouldn't use DBMS reserved words to name your tables. If you really want to use that name, you need to add quotes around it.

SELECT *
FROM `order`
WHERE ...

CodePudding user response：

Assuming order_date is type DATETIME, you can use the DATE() function to just capture the date portion:

WHERE DATE(order_date) >= $DATE_FROM 
AND DATE(order_date) <= $DATE_TO