CODEWITHSUNDEEP
sqlsql-server
B. Juice
B. Juice

Reputation: 51

How to incorporate date range in a WHERE clause?

I have the following SQL statement running in MS SQL Server:

SELECT 
    COUNT(*)
FROM 
    [dbo].[QueueAuditTrail] with(nolock)
WHERE
    (queueenddatetime >= '2017-03-17 14:00:00.000' 
     AND queueenddatetime < '2017-03-17 16:36:00.000') 
    AND queuetype = 7
GROUP BY
    queuetype
HAVING
    COUNT(queuetype) < 100000;

The queueenddatetime column is a UTC formatted timestamp. This SQL statement works for the given time range; however, I would like to configure the WHERE statement to reflect a timeframe that falls between "greater than or equal to current UTC time minus 20 minutes" and "less than current UTC time" (using the "YYYY-MM-DD hh:mm:ss.xxx") format. How can this be done?

Upvotes: 1

Views: 554

Answers (1)

SqlZim
SqlZim

Reputation: 38023

This looks like sql-server to me.

using dateadd() and sysutcdatetime()

select count(*)
from [dbo].[QueueAuditTrail] with(nolock)
where queueenddatetime >= dateadd(minute,-20,sysutcdatetime())
  and queueenddatetime <  sysutcdatetime()
  and queuetype = 7
group by queuetype
having count(queuetype) < 100000
;

Upvotes: 2

Related Questions