I have a new Sales Report which submits a query to the SQL Server. For instance, a user may want to see all the July Invoices. The user enters dates of July 1st to July 31st. My code appends the time component onto these dates in order to ensure that all July dates are selected:
Select * from Invoices Where InvoiceDate Between '2011-07-01 00:00:01' AND '2011-07-31 23:59:59'
I learned a long time ago that without the time component, Invoices produced on the last day of July would be excluded from the query results.
A user showed me the July report today. I was surprised to 1st August Invoices in there. When I looked closer, the Invoices that were shown all had a date of: 2011-08-01 00:00:00 So this was just 1 second outside the window specified by the query. I changed my query so that the end date was: 2011-07-31 23:00:00 and all was well.
So I have fixed the problem. I would just like to know why Sql Server would return records that were just outside the criteria I had set?