CODEWITHSUNDEEP
sql-servert-sql
secondbreakfast
secondbreakfast

Reputation: 4372

DATEDIFF check for one minute is actually between one and two minutes

I am using DATEDIFF to make sure a certain action cannot happen more than once a minute.

SELECT COUNT(*) 
FROM FOOD_ACTION
WHERE 
DATEDIFF(MINUTE,INSERT_DT,GETDATE()) <= 1

INSERT_DT is a date column with the same format that GETDATE() returns

If anything is returned from this query, the action is not allowed to happen.

The thing is, it only kind of works. I've had it range from a minute and 10 seconds all the way up to almost two minutes before it will let the action happen again. There doesn't seem to be any consistency in if its closer to one minute or two, and I've confirmed that GETDATE() returns the same exact time as my application does when it sets the INSERT_DT column.

What's up with the inconsistent and incorrect timing?

Upvotes: 1

Views: 3401

Answers (2)

Cato
Cato

Reputation: 3701

SELECT COUNT(*) 
 FROM FOOD_ACTION
  WHERE 
   DATEDIFF(MILLISECOND,INSERT_DT,GETDATE()) <= 60000

also why access all data to indicate nothing should happen? Better to do

SELECT 1 as marker 
  WHERE 
   DATEDIFF(MILLISECOND,INSERT_DT,GETDATE()) <= 60000

then if 1 is returned, use that as an indication, that way read no data and cut network traffic possibly

Upvotes: 1

Andrew Bickerton
Andrew Bickerton

Reputation: 478

When you do a datediff, T-SQL will look for when the time/date part boundaries have crossed to increment the difference.

So with: 

DECLARE @StartDate DATETIME = '2000-01-01T12:51:59.999',
        @EndDate DATETIME = '2000-01-01T12:52:00:000'

SELECT DATEDIFF(minute, @StartDate, @EndDate)

you will get a result of: 1
even though there was only 1ms difference between the dates.

In practice I solve this issue by going to a lower granularity and look for the cross over myself, ie:

DECLARE @StartDate DATETIME = '2000-01-01T12:51:59.999',
        @EndDate DATETIME = '2000-01-01T12:52:00:000'

SELECT 
   CASE WHEN DATEDIFF(second, @StartDate, @EndDate) >= 59 THEN CAST(1 as bit)
        ELSE CAST(0 as bit)
   END as InNewMinute

That'll give 0 as obviously 1ms is not 1 minutes difference.

Note: the code above is obviously only for handling 1 minute difference

See: MSDN DATEDIFF (Transact-SQL) (look at datePart Boundaries)

Upvotes: 5

Related Questions