SQL Date Filter: Return results when start date = end date

: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

Answer

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 condition:

OR CONVERT (datetime, date) = :startDate

Advertisement

Answer