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

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.

Sulfonation

Instead of asking whether something is correct or not, just try it. Are the results what you expect? If so, then you've done it right. If not please come back and post what you are doing, what you expect and what you are getting instead. – Taoism

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);

Diagnosis

I got this error #1305 - FUNCTION <db_name>.DATEADD does not exist – Sherwin

@AminahNuraini Well, where did you learn that you prefix DATEADD() with a database name? Are you using MySQL? This question is about SQL Server. – Diagnosis

ah yah, I am using MySQL. Sorry – Sherwin

The aversion to the BETWEEN statement is subjective. You make a good point about how it may be misleading, but the same is true about being careless with your operators. BETWEEN is a range from A to B, in that direction. Stating that you'd like to pull records from a starting point (A) of 3 months ago, until an ending point of today, seems very straightforward while using this statement. Not placing things in the order of evaluation is a bigger concern to me. – Rosenquist

@Rosenquist Did you read the link I shared? Also see this post. It's a lot less about carelessness like putting the arguments in the wrong order, and a lot more about defining exactly what constitutes the "end" of a range, most notably the impact any data type changes to tables, parameters, variables, etc. - which might be beyond your control or even knowledge - will have on the accuracy of your queries. – Diagnosis

This is exactly what i've been looking for. So glad you've answered the TopOfHour section. – Warwick