0

I'm currently facing the following problem:

  • I want to select all the records from my database table LocationUpdates for a specific RFIDTagID AND a ScannedTime smaller then 2 min compared to the current time.
  • I am giving sql two parameters: 1. RFIDTagID (Needed to select only results in the database with this tagID, 2. ScannedTime (CurrentTimeStamp).
  • Now I want to ask the databse: Give me all records for RFIDTagID 123456789 where the ScannedTime max 2 min earlier is than the second parameter ScannedTime.
  • When SQL returns results: Than don't add the row.
  • Whenm SQL doesn't return results: Than you have to add the row.

I am making a stored procedure to perform this task. It looks like the following:

  • CREATE PROCEDURE
  • SELECT COUNT(*) FROM dbo.LocationUpdates updates WHERE updates.RFIDTagID = @RFIDTagID AND DATEDIFF(MINUTE, @ScannedTime, updates.ScannedTime) < 2
  • IF @@ROWCOUNT = 0 THEN PERFORM SOME TASKS AND ADD THE ROW
  • ELSE DO NOTHING

I have the following data in my database:

160 300833B2DDD9014035050005    18-7-2013 11:18:44
161 300833B2DDD9014035050005    18-7-2013 11:19:50
162 300833B2DDD9014035050005    18-7-2013 11:24:03
163 300833B2DDD9014035050005    18-7-2013 13:38:50
164 300833B2DDD9014035050005    18-7-2013 13:39:29
165 300833B2DDD9014035050005    1-1-1900 0:00:00

AND When I execute the following query (With the currentdate):

DECLARE @return_value Int
DECLARE @currDate DATETIME
SET @currDate = GETDATE()

EXEC    @return_value = [dbo].[INSERT_LOCALROW]
        @RFIDTagID = N'300833B2DDD9014035050005',           
        @ScannedTime = @currDate

SELECT  'Return Value' = @return_value

GO

This query returns the following result: 6 rows But I am expecting to get 0 rows in return as none of the result is two minutes different then the current time.

Someone has any suggestions?

EDIT

I have found already the answer:

SELECT COUNT(*) FROM dbo.LocationUpdates updates WHERE updates.RFIDTagID = @RFIDTagID AND DATEDIFF(MINUTE, @ScannedTime, updates.ScannedTime) > -2

The function DateDiff gives a negative int when you compare a newer date with an older date, so it will return -2 when the time in the database is two minutes earlier then the current time.

joostvandriel
  • 107
  • 1
  • 1
  • 7

1 Answers1

1

I would replace

DATEDIFF(MINUTE, @ScannedTime, updates.ScannedTime) < 2

Cause if the second argument is bigger than the third argument (the dates), you will have a negative result. And... a negative result is smaller than 2.

by

updates.ScannedTime > DATEADD(MINUTE, -2, @ScannedTime)

or invert parameters

DATEDIFF(MINUTE,  updates.ScannedTime, @ScannedTime) < 2
Raphaël Althaus
  • 59,727
  • 6
  • 96
  • 122