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

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to simplify my SQL statement? 14 52
SQL query with cast 38 42
SSIS I need to get the most old file from a folder in the SSIS package. 3 23
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

805 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