DateTime Between on MySQL Not Working

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

LVL 6
r3nderAsked:
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.

Ray PaseurCommented:
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
r3nderAuthor Commented:
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
Ray PaseurCommented:
Can you please show us the code that generates the error and the exact text of the error message?  Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

r3nderAuthor Commented:
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

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
Kim WalkerWeb Programmer/TechnicianCommented:
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
r3nderAuthor Commented:
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
Kim WalkerWeb Programmer/TechnicianCommented:
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
PortletPaulfreelancerCommented:
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
PortletPaulfreelancerCommented:
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
r3nderAuthor Commented:
No one offered a solution
0
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
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.