:startDate and :endDate are given value from html input and passed as parameters with AJAX. I want to return notifications BETWEEN :startDate and :endDate, but also return a notification if startDate is equal to endDate.(A user enters the same date for both input boxes).
SELECT name, FK_userID, subject, messageText, recipients, CONVERT (varchar, date) as date FROM notifications LEFT JOIN users ON users.userID = notifications.FK_userID WHERE date BETWEEN :startDate AND :endDate OR CONVERT (datetime, date) = :startDate ORDER BY CONVERT (datetime, date, 100) desc
The OR statement breaks the first condition. The query works with just the BETWEEN statement. If a user inputs the same date value for startDate and endDate, I would expect the OR part of the query to return any notifications that have the same date value as was entered into both input boxes. Expected output would look like: startDate = 10/10/2020 endDate = 10/10/2020
returns any notifications with the date value 10/10/2020
You are saying:
The query works with just the BETWEEN statement.
and this is true, because BETWEEN has inclusive range meaning that
date BETWEEN :startDate AND :endDate
is the same as:
date >= :startDate AND date <= :endDate
and that’s why there is no need by your
OR CONVERT (datetime, date) = :startDate