Go Premium for a chance to win a PS4. Enter to Win


Incorrect Query Results

Posted on 2011-09-26
Medium Priority
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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

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…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Integration Management Part 2
Screencast - Getting to Know the Pipeline

963 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