Solved

SQL Date issue

Posted on 2014-10-28
6
147 Views
Last Modified: 2014-10-29
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
0
Comment
Question by:Murray Brown
[X]
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
6 Comments
 
LVL 66

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.
0
 
LVL 32

Expert Comment

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

Author Comment

by:Murray Brown
ID: 40408894
The fields are DateTime
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 49

Accepted Solution

by:
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
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
0
 

Author Closing Comment

by:Murray Brown
ID: 40409954
thanks very much
0
 
LVL 32

Expert Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

695 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