DateTime Between on MySQL Not Working

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?

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

Can you please show us the code that generates the error and the exact text of the error message?  Thanks.

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.

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") + "

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?

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'
;

Select allOpen 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'
          )
;

Select allOpen 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
If you use BETWEEN and get the dates backward you will get NO matches.

e.g. this will NOT work: but this WILL WORK:
Then to cap it off: you should NOT USE BETWEEN for date ranges anyway for more on this topic please see: "Beware of Between"

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'
     )
;

Select allOpen 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.)

No one offered a solution