SQL Date issue

Posted on 2014-10-28
Last Modified: 2014-10-29
Why do the following two SQL statements yield different results? The date part is the only difference

SELECT  c.School as School, Sum(b.[AMOUNT]) As [PAYMENTS|AMOUNT] FROM [CUSTOMERS] a LEFT JOIN [PAYMENTS] b ON (a.[CustID] = b.[LINK ID]) LEFT JOIN [vCustomerID_School] c ON (a.[CustID] = c.[CustomerID]) WHERE b.[DATE]='2014 Sep 15' GROUP BY  c.School

SELECT  c.School as School, Sum(b.[AMOUNT]) As [PAYMENTS|AMOUNT] FROM [CUSTOMERS] a LEFT JOIN [PAYMENTS] b ON (a.[CustID] = b.[LINK ID]) LEFT JOIN [vCustomerID_School] c ON (a.[CustID] = c.[CustomerID]) WHERE b.[DATE]>'2014 Sep 14' And b.[DATE]<'2014 Sep 16' GROUP BY  c.School
Question by:murbro
LVL 65

Expert Comment

by:Jim Horn
ID: 40408805
>WHERE b.[DATE]='2014 Sep 15'
>WHERE b.[DATE]>'2014 Sep 14' And b.[DATE]<'2014 Sep 16'

>Why do the following two SQL statements yield different results?
Not sure what you are expecting here.  The only way the queries would return the same results is if all rows between 2014-08-14 and 2014-09-16 are dated 2014-09-15.

btw is the data type of column [DATE] date, or some kind of char?  If date, there may be some implicit conversion issues converting the string '2014 Sep 14' to date format in play.

btw SQL expert PortletPaul's article Beware of Between might be a good read here.
LVL 32

Expert Comment

ID: 40408876
Does the [DATE] field contain a time element?

Author Comment

ID: 40408894
The fields are DateTime
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

LVL 48

Accepted Solution

PortletPaul earned 500 total points
ID: 40409899
The second is "better" because it is "sargable"

a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query

But the second is probably not entirely correct.

The reason the 2 queries do not produce the same result is because the data contains TIME references.

2014 Sep 14 13:59:57 +23456
2014 Sep 15 00:00:00 +00000 **
2014 Sep 15 00:00:00 +00001
2014 Sep 15 09:12:24 +56789
2014 Sep 15 23:59:59 +12345

only ONE of those EQUALS '2014 Sep 15'  (see ** above)

ALL of those are >'2014 Sep 14' And <'2014 Sep 16'

Notice that '2014 Sep 14 13:59:57 +23456' is INCLUDED!

I think that to get the correct result (i.e. assuming you want EVERY record for 2014 Sep 15 then the where clause should be:

WHERE b.[DATE]>='20140915' And b.[DATE]<'20140916'

Note: greater than or equal to

Also note I changed from using a 3 character month name to using digits only. It is not "safe" to just assume the language settings, and the safest of all date literals to use with SQL Sever is YYYYMMDD
2014 Sep 15

Jim has kindly pointed you to my article "Beware of Between",
in addition I would suggest
The ultimate guide to the datetime datatypes by Tibor Karaszi by Itzik Ben-Gan

Author Closing Comment

ID: 40409954
thanks very much
LVL 32

Expert Comment

ID: 40410534
And now you know why I asked if the field contained a time element :-)

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now