Any way to narrow report by TIME range?

[RSAAdmin]

I am interested in creating a report that shows the user activity between 10 AM and 2 PM every day for 8 months.  I would like to have this show in as few reports as possible.  I cannot seem to figure out how to do this without having to run individual reports for each day or creating a SQL statement that uses Date/Time >= and <= (but again, it is by day, so several OR statements for each day).

Of course I can run the report by month and then manually trim out the data not needed, but that's a PITA...

Has anyone conquered this problem?

[CharlesBeierle]

What event source type are you running the report against? Which table?

[RSAAdmin]

My recommendation would be to use a SQL function in the where clause.  Specifically use datename which takes two parameters (Date component, SQL Column name).

Here are the available date components you can use:

year      yy    1753-9999

quarter   qq    1-4

month     mm    1-12

week      wk    1-54

day       dd    1-31

dayofyear dy    1-366

weekday   dw    1- 7 (Sunday-Saturday)

hour      hh    0-23

minute    mi    0-59

second    ss    0-59

 So, assuming you were using the WindowDeviceStamp column for the date data you might format your where clause like this:

datename(hh, WindowDeviceStamp) >= 10 AND datename(hh, WindowDeviceStamp) < 2

Cheers

[RSAAdmin]

That's awesome.   Where is this documented at / how did you come to know about this?

Thanks for posting it, I've got several use cases for this already.

ryan