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.
hh
. Here's why.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);