SQL - Select records for the same day between particular data range

Question

I need to check the SQL date range between some values. Example. To check records added between last 2 to 1 hours.

Is the below query correct:

SELECT * 
FROM tablename 
WHERE dtdatetime BETWEEN DATEADD(hh, -1, GETDATE()) AND DATEADD(hh, -2, GETDATE())

Could you please provide suggestions.

Answer 1

• don't use lazy shorthand like hh. Here's why.
• don't use BETWEEN. Here's why. It requires the first argument to be the smaller one anyway - your problem is actually that you are saying WHERE 2 BETWEEN 3 AND 1 and it will only return true if you flip it to WHERE 2 BETWEEN 1 AND 3. Go ahead and try it - the smaller argument needs to be first. But really, just stop using BETWEEN for date range queries anyway.

Here is what your query should look like:

WHERE dtdatetime >= DATEADD(HOUR, -2, GETDATE())
  AND dtdatetime <  DATEADD(HOUR, -1, GETDATE());

Note that this will give different results depending on what time you run it during the current hour. For example if you run it at 12:32 it will give data >= 10:32 and < 11:32. If you want data >= 10:00 and < 11:00, whether it is run at 12:04 or 12:32 or 12:59, then you want this instead:

DECLARE @TopOfHour DATETIME;
SET @TopOfHour = DATEADD(HOUR, DATEPART(HOUR, GETDATE()), DATEDIFF(DAY, 0, GETDATE()));

SELECT ...
WHERE dtdatetime >= DATEADD(HOUR, -2, @TopOfHour)
  AND dtdatetime <  DATEADD(HOUR, -1, @TopOfHour);