Solved

SQL Date issue

Posted on 2014-10-28
6
144 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:murbro
6 Comments
 
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.
0
 
LVL 32

Expert Comment

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

Author Comment

by:murbro
ID: 40408894
The fields are DateTime
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 48

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:murbro
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

778 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