Enhancing SQL Query for Date Range in WHERE clause

Primary tabs

Hi All -

I was wondering if the below query could be enhanced to automatically look back 24 hours from the current datetime the query is run. As is now I, of course, have to update the date range in the WHERE clause manually.

The query is just getting all MessageBodyClassNames, counting them and then doing an AVG on TimeCreated and TimeProcessed. Nothing too complex.


SELECT MessageBodyClassName, count(ID) as Count_Of_Messages, avg(datediff(ss, TimeCreated, TimeProcessed)) as avg_processing_time_in_seconds

FROM %PARALLEL Ens.MessageHeader

WHERE TimeCreated BETWEEN  '2017-04-18 00:00:00' AND  '2017-04-19 23:59:59'

GROUP BY MessageBodyClassName


Any input would be greatly appreicated!





Use DATEADD function to add/remove dates, and NOW to get current date:


Returns yesterday date:

2017-04-19 20:11:00