CODEWITHSUNDEEP
sqlsql-server
Dax Durax
Dax Durax

Reputation: 1657

How to get records between 24 and 36 hours in SQL Server

I am trying to get records between 24 and 36 hours.

So far I have :

select * from tablename where DATEDIFF(DAY, dateColumn, GETDATE())>0

This returns me all records older than 24 hours. I am looking to get records older than 24 but no older than 36.

Thanks

Upvotes: 0

Views: 3845

Answers (5)

Karl Kieninger
Karl Kieninger

Reputation: 9129

Note: While these are the first solutions to come to mind, they are suboptimal as pointed out in the comments. See @MatBailie's answer for a solution that would be preferable.

While these are natural and might be okay in some limited use, you really should prefer a solution that is Search ARGument ABLE.

Sargable

In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE.

Original answers:

Just add another condition:

select * 
  from tablename 
 where DATEDIFF(DAY, dateColumn, GETDATE())>0
   and DATEDIFF(HOUR, dateColumn, GETDATE()) <= 36

or

select * 
  from tablename 
 where DATEDIFF(HOUR, dateColumn, GETDATE()) BETWEEN 24 AND 36

Note: In addition to being non-sargable this BETWEEN also includes records 24 hours old when in fact OP askks for wants older than 24. [OP use between a couple times, but clarifies that it isn't an inclusive SQL BETWEEN, but rather a semi-inclusive between that must be implemented with > and <=. ]

Upvotes: 2

MatBailie
MatBailie

Reputation: 86716

So far all the answer here do something like WHERE a.function(date_field) > 0; they place a function around your search field.

Unfortunately this means that the RDBMS's optimiser can not use any index on that field.

Instead you may be recommended in moving the calculations "to the right hand side".

SELECT
  *
FROM
  tablename
WHERE
      dateColumn >= DATEADD(HOUR, -36, GETDATE())
  AND dateColumn <  DATEADD(HOUR, -24, GETDATE())

This format calculates two values, once, and then can do a range seek on an index. Rather than scanning the whole table, repeating the same calculations again and again.

Upvotes: 5

xbb
xbb

Reputation: 2163

SELECT * FROM tablename WHERE DATEDIFF(hh,dateColumn,GETDATE()) between 24 and 36

Upvotes: 0

Jaime Garc&#237;a P&#233;rez
Jaime Garc&#237;a P&#233;rez

Reputation: 953

Use between:

select * from tablename where DATEDIFF(hour, dateColumn, GETDATE()) between 24 and 36

Upvotes: 0

Tomas Pastircak
Tomas Pastircak

Reputation: 2857

You must also specify the 36 hours:

select * from tablename where DATEDIFF(DAY, dateColumn, GETDATE())>0 AND DATEDIFF(hh, dateColumn, GETDATE())<=36

Upvotes: 0

Related Questions