Enhancing SQL Query for Date Range in WHERE clause

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!

 

Thanks,

Blake

  • 0
  • 0
  • 1694
  • 0
  • 1

Answers

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

SELECT DATEADD('d', -1, NOW())

Returns yesterday date:

2017-04-19 20:11:00