SQL Date issue

Hi
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
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
>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.
awking00Information Technology SpecialistCommented:
Does the [DATE] field contain a time element?
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
The fields are DateTime
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
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
http://en.wikipedia.org/wiki/Sargable

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.

e.g.
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
hence
2014 Sep 15
becomes
20140915

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
http://sqlmag.com/t-sql/t-sql-best-practices-part-2 by Itzik Ben-Gan

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks very much
awking00Information Technology SpecialistCommented:
And now you know why I asked if the field contained a time element :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.