TIMEDIFF 5 minutes problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

TIMEDIFF 5 minutes problem

Author

Topic

derozza
Starting Member

13 Posts

Posted - 2011-01-23 : 19:39:16

Hello codegurus and codemasters... I have a problem with TIMEDIFF statement, hope you awesome people can help me...

The SELECT statement 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.

Thanks in advance...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-23 : 20:17:37

TIMEDIFF(res1.entrystamp,res2.moded_time) <= '00:05:00'
AND TIMEDIFF(res1.entrystamp,res2.moded_time) >= '00:00:00'

I think because the time diff is actually negative (ignoring the datepart obviously), you need to put a floor on the where clause to prevent negative results. (-53+ minutes is less than 5 minutes)

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources