Click here to Skip to main content
11,803,561 members (73,233 online)
Click here to Skip to main content

Tagged as

Programming for Date Ranges

, 25 Sep 2009 Ms-PL 9.4K 6
Rate this:
Please Sign up or sign in to vote.
How to program for date ranges

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

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:

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:

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.


This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)


About the Author

Charles Boyung
Architect Nexus Technologies, LLC
United States United States
I have been working in the field of software development since 1999. With a degree in Computer Engineering from the Milwaukee School of Engineering, I try to provide a strong results-oriented approach to software development. I have worked with a variety of industries, including healthcare, magazine publishing and retail. After having worked for corporations of varying sizes for nearly ten years while also providing custom software solutions to individuals and small companies, I left the corporate world to provide expert, high-quality software solutions to a broader range of companies full-time. I am also a Certified Usability Analyst with Human Factors International, committed to providing the best possible experience to the users of your website or application.

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.151002.1 | Last Updated 25 Sep 2009
Article Copyright 2009 by Charles Boyung
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid