Extending an SQL select with date bounds I found that the end date wasn’t including the given date, rather only entries upto the day before were being selected.
How to include the whole of the second date without adding times?
To add the date bound to an SQL select I was using two text boxes, one for the start date and one for the end date.
I had also added a date picker to each, to ensure that the date is entered in the correct format, with no ambiguous dates. For example is 8/1/2018 8th January or 1st August?
Here’s the gist of my SQL . I am select items from the meetings table where the start date is after, or on the passed value @StartDate and similarly the end date is before or on the pased value of @EndDate.Taking the values entered in the text boxes as a part of my SQL:
SELECT
*
FROM
Meeting
WHERE
StartDate >= @StartDate
AND EndDate <= @EndDate
I found that entries which had an EndDate value the same as the entered text box value weren’t begin selected.
The reason was that I was only passing the date, not the time. As such it was the equivalent to the very start of the day, and so prior to the entries ending on the day.
To resolve the issue I needed to set the end date to just before midnight, ie right at the end of the day.
The approach I took was to add a day and then subtract a tick.
EndDate = EndDate.AddDays(1)
EndDate = EndDate.Addticks(-1)
With this modification in place I was passing the end of the day and so all of the entries of that day were being included in my select.
