Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

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
0
Murray Brown
Asked:
Murray Brown
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
awking00Commented:
Does the [DATE] field contain a time element?
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
The fields are DateTime
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
PortletPaulCommented:
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
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks very much
0
 
awking00Commented:
And now you know why I asked if the field contained a time element :-)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now