Our projects require a lot of database interaction as any intermediate to large web site requires. A common problem with databases are the DateTime datatypes.

DateTime datatypes within WHERE clauses frequently give people the wrong results. I see this often. I am having to fix a lot of reports now to correct this issue. Here is the offending statement:

WHERE CreatedDate between @BeginDate and @EndDate

By default, a datetime value will give the time portion of “00:00:00″; the full value looks like this – “1/30/2009 00:00:00″.

See the problem with this WHERE statement – “WHERE CreatedDate BETWEEN ‘1/1/2009 00:00:00′ AND ‘1/30/2009 00:00:00′”? The problem is that you will not get data for the date of January 30th since that value is only up to midnight on the 30th.

Here are two ways of fixing it. Both of these methods depend on the programmer modifying the @EndDate parameter.

WHERE CreatedDate BETWEEN ‘1/1/2009 00:00:00′ AND ‘1/30/2009 23:59:59′.  --This takes you to the end of the day on the 30th.

WHERE CreatedDate BETWEEN ‘1/1/2009 00:00:00′ AND ‘1/31/2009 00:00:00′  --This takes you to the start of the next day.

An easy fix for this (examples are using Transact SQL) is the following:

WHERE CreatedDate BETWEEN @BeginDate and ADDDATE(day,1,@EndDate)

This turns the EndDate parameter into the next day at midnight; it is like the second example above.