Programming for Date Ranges

If you are anything like me, you have seen plenty of SQL statements like this:

SELECT *
FROM MyTable
WHERE CreateDate BETWEEN @StartDate AND @EndDate

Pretty simple and self explanatory, right? To a programmer, yes it is. However, when you start building a UI for the user to enter those start dates and end dates, how do you do it? A typical UI might have something like this:

What do those labels mean to the user? For most users, those labels are taken to mean "From the start of <Start Date> through the end of <End Date>", or to be more exact, from midnight on <Start Date> until 11:59:59 pm on <End Date>. However, I have seen too many examples to count where programmers just take those two dates and plug them into the @StartDate and @EndDate in my example above. This is a pretty big problem for the user because now when they enter an end date, they are not getting data until 11:59:59 pm on <End Date>. They are only getting data until midnight on <End Date>. The programmers in these situations just leave it to the users to figure out that they need to add one to the end date to get the true data range that they want to receive. Why do this? It is simple enough for the programmer to just do this:

SELECT *
FROM MyTable
WHERE CreateDate BETWEEN @StartDate AND DATEADD(d, 1, @EndDate)

Often, this is good enough, because it gives you from midnight on <Start Date> through midnight on the day after <End Date>. However, there are several instances that I have dealt with where that extra second of data is going to provide inaccurate results, because the user literally needs only the data from a very specific range of dates, and even a single extra record in a report could be disastrous. So, to alleviate this issue, the best course of action within your SQL would be to do something like this:

SELECT *
FROM MyTable
WHERE CreateDate >= @StartDate AND CreateDate < DATEADD(d, 1, @EndDate)

Yes, it is a little bit more code, but what do a few extra characters cost when compared to the potential cost to your users, both in lack of UI understanding as well as potential errors in their data? To me, this one has always been a no-brainer.

CodeProject