More like general MySQL than vb.net itself, but still need help.


The MySQL code below:

SELECT * 
FROM (SELECT resit_no,SUBSTRING(resit_time,1,19) AS entrystamp
      FROM DB1.receiptdetails
      WHERE terminal LIKE '%99' 
      AND resitdate BETWEEN '2010-12-01' AND '2011-01-31'
      AND productcode BETWEEN '91000001' AND '91000009') res1, 
     (SELECT expdate_resitno, SUBSTRING(TimeModified,1,19) AS moded_time 
      FROM DB2.mtransactionstatus
      WHERE expDt_resno LIKE '% - 99%'
      AND DTcreated BETWEEN '2010-12-01' AND '2011-01-31') res2
WHERE res1.resitno = SUBSTRING(res2.expDt_resno,11,8)
AND res1.entrystamp <> res2.moded_time
AND TIMEDIFF(res1.entrystamp,res2.moded_time) <= '00:05:00'

produces:

--------------------------------------------------------------------------
resitno  | entrystamp	       | expdt_resno	    | 	moded_time
---------|---------------------|------------------------------------------
99002593 | 2010-12-03 11:32:32 | 11/2012 - 99002593 | 2010-12-03 11:32:33 
99000566 | 2010-12-03 15:55:09 | 12/2011 - 99000566 | 2010-12-03 15:55:10 
99000567 | 2010-12-03 17:06:40 | 12/2011 - 99000567 | 2010-12-03 17:06:41 
99000585 | 2010-12-15 10:28:00 | 12/2011 - 99000585 | 2010-12-15 10:27:59 
99000581 | 2010-12-14 15:54:45 | 01/2012 - 99000581 | 2011-01-04 15:40:54 
99000581 | 2010-12-14 15:54:45 | 01/2012 - 99000581 | 2011-01-06 15:01:51

notice the two rows at the bottom. the difference between entrystamp and moded_time is higher than 5 minutes. why would it happen and how to correct it?

both table and database use InnoDB.

Nevermind this, it's already solved. the timediff returns negative data which is also less than 5 minutes.

This question has already been answered. Start a new discussion instead.