Don't Make Assumptions about Dates in SQL Server

Most ASP developers aren't Database Administrators by nature, and consequently, we sometimes make mistaken assumptions when we put together even the most deceptively simple SQL statements.

For example, you might expect that you could retrieve all the books published in the month of June, 2000 by executing the following statement:

SELECT title, pubdate
FROM titles
WHERE pubdate
BETWEEN '6/1/2000' and '6/30/2000'

In fact, if you omit the time portion of a DATETIME field, SQL Server assumes that you mean midnight; hence, the query above would actually omit the last day of the month. Obviously, this isn't what you probably had in mind.

To correct the query, either increment the ending date by one day or provide the time portion, 23:59:59.

Source: Jason Fisher
Viewed 7589 times