Solved

DateTime Between on MySQL Not Working

Posted on 2014-09-03
10
510 Views
Last Modified: 2014-09-09
resultsI have a MySQL Query and the date time does not seem to be working does anyone  know why it seems to disreguard the datetime between statements (attached is the image of the results)
 SELECT j.JobID,jde.ToolFailureTime,jde.RigFailureTime ,jue.FailureTime, j.Company, j.Rig, j.RigNumber, j.WellName, jde.RigFailure, jde.ToolFailure, jue.Failure, jue.Notes AS 'UH Notes', jde.Notes AS 'DH Notes'  
FROM `Job` AS j  
JOIN JobDownholeEquipment AS jde ON j.JobID = jde.JobID  
JOIN JobUpholeEquipment AS jue ON j.JobID = jue.JobID  
WHERE 
 jde.RigFailure = TRUE 
OR jde.ToolFailure = TRUE
OR jde.RigFailure = TRUE


AND jde.ToolFailureTime BETWEEN '2014-09-03 12:00:00' AND  '2014-09-03 11:59:59'
OR jde.RigFailureTime BETWEEN '2014-09-03 12:00:00' AND  '2014-09-03 11:59:59'
OR jue.FailureTime BETWEEN '2014-09-03 12:00:00' AND  '2014-09-03 11:59:59'

ORDER BY j.Company
<!----- I also tried-------->
OR jde.ToolFailureTime BETWEEN str_to_date('2014-09-03','%Y-%m-%d') AND str_to_date ( '2014-09-03','%Y-%m-%d')
OR jde.RigFailureTime BETWEEN  str_to_date('2014-09-03','%Y-%m-%d') AND  str_to_date( '2014-09-03','%Y-%m-%d')
OR jue.FailureTime BETWEEN str_to_date('2014-09-03','%Y-%m-%d') AND  str_to_date('2014-09-03','%Y-%m-%d')
ORDER BY j.Company

Open in new window

0
Comment
Question by:r3nder
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40301446
I don't have time to create a test case now, but I can give you some theory that might explain it.  First of all, are the columns defined as DATETIME?  That's job #1.

Consider the values in FailureTime.  0001-01-01 00:00:00 does not make sense to me.

Consider the values in ToolFailureTime.  2014-06-09 13:09:48 makes perfect sense.  But a query that looks for data on June 1st-9th with something  like this will not find that row because the time in that column makes the value greater than 2014-06-09, and therefore outside the BETWEEN constraint

... WHERE ToolFailureTime BETWEEN 2014-06-01 AND 2014-06-09

You can find that row by adding one day to get 2014-06-10.  You can also find that row with this:

... WHERE ToolFailureTime BETWEEN 2014-06-01 AND 2014-06-09 23:59:59

You can also find that row with this:

... WHERE ToolFailureTime >= 2014-06-01 AND ToolFailureTime < 2014-06-10

Does that help?
0
 
LVL 6

Author Comment

by:r3nder
ID: 40301674
Yes the 3 fields (jde.ToolFailureTime ,jde.RigFailureTime  jue.FailureTime) are datetime
The data 00-00-00 00:00:00 and 0001-01-01 00:00:00 are values the debveloper before me inserted in the database if an Item in the program he wrote was not selected.
The image above was the result after the query was run
in my code it is giving me an error Unable to convert MySQL date/time value to System.DateTime
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40301939
Can you please show us the code that generates the error and the exact text of the error message?  Thanks.
0
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.

 
LVL 6

Accepted Solution

by:
r3nder earned 0 total points
ID: 40301978
Fixed I did a 3 queries and unioned then all
 query += " SELECT j.JobID, jde.RigFailureTime As 'FailureTime', ";
                query += " CASE jde.RigFailure ";
                query += " WHEN 1 THEN 'RIG' End as 'Tool' ";
                query += " ,j.Company, j.Rig, j.RigNumber, j.WellName,  jde.Notes AS 'Notes' ";
                query += " FROM Job as j ";
                query += " JOIN JobDownholeEquipment AS jde ON j.jobid = jde.jobid ";
                query += " WHERE j.Company <> 'TestCompany' ";
                query += " AND jde.RigFailure = 1 AND jde.RigFailure IS NOT NULL ";
                query += " AND jde.toolFailureTime Between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'  ";
                query += " UNION ";
                query += " SELECT j.JobID, jde.ToolFailureTime As 'FailureTime',  ";
                query += " CASE jde.ToolFailure ";
                query += " WHEN 1 THEN 'TOOL' End as 'Tool' ";
                query += " ,j.Company, j.Rig, j.RigNumber, j.WellName,  jde.Notes AS 'Notes' ";
                query += " FROM Job as j ";
                query += " JOIN JobDownholeEquipment AS jde ON j.jobid = jde.jobid  ";
                query += " WHERE j.Company <> 'TestCompany' ";
                query += " AND jde.ToolFailure = 1 AND jde.ToolFailure IS NOT NULL ";
                query += " AND jde.toolFailureTime Between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'  ";
                query += " UNION ";
                query += " SELECT j.JobID, jue.FailureTime As 'FailureTime',  ";
                query += " CASE jue.Failure ";
                query += " WHEN 1 THEN 'UP' End as 'Tool' ";
                query += " ,j.Company, j.Rig, j.RigNumber, j.WellName,  jue.Notes AS 'Notes' ";
                query += " FROM Job as j ";
                query += " JOIN JobUpholeEquipment AS jue ON j.jobid = jue.jobid  ";
                query += " WHERE j.Company <> 'TestCompany' ";
                query += " AND jue.Failure = 1 AND jue.Failure IS NOT NULL ";
                query += " AND jue.FailureTime Between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'  ";

Open in new window

0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 40301986
Did you intend to select between 12 noon and 1 second before 12 noon? 12:00:00 is 12 noon. 11:59:59 is one second before 12 noon. If you're looking for midnight, that'd be 23:59:59.
0
 
LVL 6

Author Comment

by:r3nder
ID: 40302010
if is converted to 00:00:00 and 23:59:59
in the lines like this one jue.FailureTime Between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "
0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 40304790
Your last comment looks incomplete. You never answered my question if you intended to search for a time span of negative one second. Do you still get the error even when you're searching for a significant span of time?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40304965
There are several problems in your where clause.

The first is that you have an unconstrained ORs and what you think is being requested, isn't what is happening.

This is what is happening right now:
SELECT
      *
FROM x
WHERE jde.RigFailure = TRUE
      OR jde.ToolFailure = TRUE
      OR (
         jde.RigFailure = TRUE
         AND jde.ToolFailureTime BETWEEN '2014-09-03 12:00:00' AND '2014-09-03 11:59:59'
         )
      OR jde.RigFailureTime BETWEEN '2014-09-03 12:00:00' AND '2014-09-03 11:59:59'
      OR jue.FailureTime BETWEEN '2014-09-03 12:00:00' AND '2014-09-03 11:59:59'
;

Open in new window

But I believe what you are expecting is:
SELECT
      *
FROM x
WHERE (
         jde.RigFailure = TRUE
      OR jde.ToolFailure = TRUE
      OR jde.RigFailure = TRUE
      )
      AND (
             jde.ToolFailureTime BETWEEN '2014-09-03 12:00:00' AND '2014-09-03 11:59:59'
          OR jde.RigFailureTime BETWEEN '2014-09-03 12:00:00' AND '2014-09-03 11:59:59'
          OR jue.FailureTime BETWEEN '2014-09-03 12:00:00' AND '2014-09-03 11:59:59'
          )
;

Open in new window

BUT ALSO you have a major problem with the use of between. It has already been identified that you are using midday (12:00:00) and one second before midday (11:59:59) but you you are currently using these backward you MUST have the earlier date FIRST

see a demonstration of this here: http://sqlfiddle.com/#!9/8464e/2

If you use BETWEEN and get the dates backward you will get NO matches.

e.g. this will NOT work:
select
*
from jobs
where jobstart BETWEEN '2014-09-03 12:00:00' AND  '2014-09-03 11:59:59' -- wrong way around
;
but this WILL WORK:
select
*
from jobs
where jobstart BETWEEN '2014-09-03 11:59:59' AND  '2014-09-03 12:00:00'
;

Then to cap it off: you should NOT USE BETWEEN for date ranges anyway for more on this topic please see: "Beware of Between"
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40307147
I'm guessing as to your real intention, but I think this may be the where clause you want:
WHERE (
      jde.RigFailure = TRUE
      AND jue.FailureTime >= '2014-09-03 12:00:00' AND jue.FailureTime < '2014-09-04 00:00:00'
      )
  OR (jde.ToolFailure = TRUE
      AND jde.ToolFailureTime >= '2014-09-03 12:00:00' AND jde.ToolFailureTime < '2014-09-04 00:00:00'
     )
  OR (jde.RigFailure = TRUE
      AND jde.RigFailureTime >= '2014-09-03 12:00:00' AND jde.RigFailureTime < '2014-09-04 00:00:00'
     )
;

Open in new window

It couples each failure type to the related failure date/time, and for the date/time it allows only the half-day from midday up to midnight on 2014-09-03. (Midnight itself is considered to be the start of the next day.)
0
 
LVL 6

Author Closing Comment

by:r3nder
ID: 40311605
No one offered a solution
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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

789 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