Incorrect Query Results

Posted on 2011-09-26
Last Modified: 2012-05-12
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?
Question by:TownTalk
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 36598511
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.

Accepted Solution

John_Arifin earned 500 total points
ID: 36598534

Expert Comment

ID: 36598607
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,@EndDate)
LVL 50

Expert Comment

ID: 36599001
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..

Author Comment

ID: 36599173
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.

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question