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:51notice 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.