- How to update a column with incrementing numbers
- How to get top N rows for each group?
- How to put a column into a delimited form
- How to export results of a stored procedure to a txt file
- Parsing delimited words from a column
- How to get N-th max value
- How to get a random row from a table
- How to dynamicaly rank rows
- How to get just date or just time from a datetime value
- Windows Media Player and folder.jpg Hell
Home :: Tips & Tricks :: Microsoft SQL Server
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