Using Date/Time Keywords in Reports

A manager needs a report that shows all items closed in the last month. In a report form using basic conditions, enter the following conditions in the Field Specification area:

Close Date/Time > startof_lastmonth

Close Date/Time < endof_lastmonth

Each time the report is run, the date value is recalculated and the results show all the items closed in the last month.

The following examples show how to use Date/Time keywords when you create an Advanced SQL condition using SBM–processed SQL.

• Find items submitted today:

  ("Submit Date/Time" >= startof_today) and ("Submit Date/Time" <= endof_today).

• Find items submitted this month:

  ("Submit Date/Time" >= startof_thismonth) and ("Submit Date/Time" <= endof_thismonth).

• Find items submitted more than 30 days ago:

  "Submit Date/Time" < (now - 30).

  The keyword now is substituted with the current date and time. Subtracting 30 days from the current date and time allows items that were submitted more than 30 days ago to be found.

• Find resolved items that are not verified after 14 days:

  (State = Resolved) and ("Last State Change Date" < (now - 14)).

  This query is useful for uncovering items that have had no action on for some time. It can be very useful for project management.