CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
GeoVIP
GeoVIP

Reputation: 1564

How avoid negative numbers during datediff

Have table where have time like : 15:30 , want select data from table in 15 minute interval but only possitive, I try :

select id from myTbl
where type = 2 and DATEDIFF(mi,my_time,LEFT(CAST(GETDATE() as time),5)) <= 15

For example if my_time = 15:55 and LEFT(CAST(GETDATE() as time),5)) = 16:45 in response i have -50 and its <= 15 but i need comparison only possitive , when i try ABS it dont help me because when time in response is -14 ABS take it +14 and its <=15 . So i have 28 minute interval (-14 and 14). Is it possible tu avoid all negative numbers ? and comparison only if it is possitive

Upvotes: 2

Views: 1400

Answers (2)

M.Ali
M.Ali

Reputation: 69494

A much better approach would be some thing like ...

SELECT id 
FROM myTbl
WHERE [TYPE] = 2 
AND my_time >= CAST(DATEADD(MINUTE, -15, GETDATE()) AS TIME)
AND my_time <= CAST(GETDATE() AS TIME)

Avoid using DATEDIFF and other scalar functions on your columns in where clause, as it will not allow query optimizer to make use of any indexes even if there is an index that query can benefit from , Read this article for more information SARGable functions in SQL Server

Upvotes: 4

user3608397
user3608397

Reputation: 135

select id 
from myTbl
where type = 2 
      and DATEDIFF(mi,my_time,LEFT(CAST(GETDATE() as time),5)) <= 15 
      and DATEDIFF(mi,my_time,LEFT(CAST(GETDATE() as time),5)) >=0

Upvotes: 4

Related Questions