Incorrect Query Results
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?
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?
dan_mason
I know this is potentially not much help but BETWEEN should only return records that are "less than or equal to" the upper value. I just wonder if there is a rounding issue involved - a datetime value is more precise than the values you've included in your question.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To avoid the possibility of rounding altogether you could do this instead:
Select * from Invoices Where InvoiceDate>='20110701' AND InvoiceDate<'20110801'
Of course if the user is actually specifying the dates of 1st & 31st July it would look something like this:
SELECT * FROM Invoices Where InvoiceDate>=@StartDate AND InvoiceDate<DATEADD(dd,1,@
Select * from Invoices Where InvoiceDate>='20110701' AND InvoiceDate<'20110801'
Of course if the user is actually specifying the dates of 1st & 31st July it would look something like this:
SELECT * FROM Invoices Where InvoiceDate>=@StartDate AND InvoiceDate<DATEADD(dd,1,@
what time component did you actually specify?
the article is interesting
but 23:59:59.999 gets rounded to the next day... wheras 23:59:59.997 doesn't and is usually ok..
the article is interesting
but 23:59:59.999 gets rounded to the next day... wheras 23:59:59.997 doesn't and is usually ok..
ASKER
This makes sense now..... In my query I was using 23:59:59, but the InvoiceDate field is a smalldatetime datatype, which is only accurate to the nearest minute. So my 59 seconds got rounded up to the nearest minute. Thats why I got August records in the resultset.