How do I filter items based, not on just the date, but on the time as well?

This is not as simple as it may seem.  For example, you want to see all items after January 15 at 12 pm.  If you filter first on a date and then another filter on time, say 12 pm, you will get items that are after that date AND after that time.  So you would get all items for after January 15 but only if their time is after 12 pm.  You would not see items for January 16 at 11 am.

Most report filters are on dates only.  However, occasionally there is a need to specify both a date and time.  For example, when one wants to print all work orders created after 12 noon last Friday, when there were some Work Orders printed earlier on that day that do not belong in the report.

To do this, a special filter expression is required.

1.      Select two matching date and time fields. For example, Work Order creation date and time.
2.      Determine their underlying field and alias names. You can do this by right clicking on the desired field and selecting “Field Information”.  A dialog will appear.  You want the “Field” and “Alias” values.  For the sample below we will use Work Order Creation Date and Time.
3.      Select a “Dummy” field that will be used in the same table.  Since “Dummy” fields are just placeholders, it is easiest to select one that is well known, such as Asset # or Work Order #.  Select one found in the table being selected.
4.      Determine the Dummy field’s underlying field name.
5.      Enter the expression with the proper selected fields as show below:

Here is a sample of how to do it. It uses the Work  Order creation date and time.  The dummy field used is “Work Order #”:

Dummy Field: Work Order #
Dummy Field name: wm.l_movenum

Work Order Creation Date field: wm.wm_date
Work Order Creation Time field: wm.wm_tbtime

In a report, select the ‘Filter By’ tab and add a filter.  When the filter dialog appears, select the dummy field on the left. In this example we select the “Work Order #”. Set the operator to “=”, select an expression then enter the value expression.  These are as follows:

Filter Dialog values:

Table:                  Work Order
Field:                   Work Order #
Operator:            = (equal)
Type:                 Expression
Value:                 WM.L_MOVENUM AND GETUNIVERSALTIME(WM.WM_DATE, WM.WM_TBTIME) >= GETUNIVERSALTIME(DateAsText, TimeAsText)

Where:

WM.L_MOVENUM        = Dummy field’s underlying field name. This must match the dummy field selected in the left column.
Wm.wm_date           = The underlying Date field.
Wm.wm_tbtim   = The underlying Time field.
DateAsText        = date enclosed in curly braces, e.g. {8/31/2021}.  In U.S.A. time this example is August 31,2021.
TimeAsText       = time in “HH:MM:SS” format. The enclosing quote marks and the two digit width for each are required. The separators (the colon) are optional.

HH         = hours (in 24 hour format)
MM       = minutes
SS           = seconds.

For example:

WM.L_MOVENUM AND GETUNIVERSALTIME(WM.WM_DATE, WM.WM_TBTIME) >= GETUNIVERSALTIME({3/7/2020},”09:40:00″)

This will print all work orders created after March 7,2020 at 9:40 am.

Note:

1.      The text [WM.L_MOVENUM AND] may seem unneeded, but it IS needed. It matches the Work Order # field selected on the left.  This allows the special time function to be used.
2.      The operator MUST be set to “equal” (=).