Advanced date queries for Maximo (Oracle)

Resolving The Problem

Users often want to query records based on fixed date ranges, such as "Last 7 days" or "This week" without having to enter dates every time. This shows SQL syntax that can be added to any where clause to do this dynamically.

For Oracle:

Records with a date within the last seven days to the second. Change datecolumn to the appropriate column for the table:

AND (datecolumn BETWEEN SYSDATE-7 AND SYSDATE)

Records with a date within the last seven calendar days (the TRUNC function cuts off the time component, effectively setting it to midnight):

AND (datecolumn BETWEEN TRUNC(SYSDATE-7) AND SYSDATE)

Changing the 7 to 30 in the above conditions to query for the last 30 days.

Records with a date within the last seven days to the second. Change datecolumn to the appropriate column for the table:

AND (datecolumn BETWEEN SYSDATE-30 AND SYSDATE)

Records with a date within the last seven calendar days (the TRUNC function cuts off the time component, effectively setting it to midnight):

AND (datecolumn BETWEEN TRUNC(SYSDATE-30) AND SYSDATE)

It gets a little tricky, but you can also use this type of condition to select by a specific calendar week (Sun - Sat) instead of 7 days prior to today:

Records with a datecolumn value that falls within the current week, where the week starts with Sunday:

AND (datecolumn BETWEEN (TRUNC(SYSDATE - (TO_CHAR(SYSDATE,'D')-1))) AND SYSDATE)

Records with a datecolumnvalue that falls within the previous week, from Sunday through Saturday midnight:

AND (datecolumn BETWEEN (TRUNC(SYSDATE - (TO_CHAR(SYSDATE,'D')+6))) AND (TRUNC(SYSDATE - (TO_CHAR(SYSDATE,'D')-1))))

A DBA or MAXIMO Administrator can develop useful date range queries to meet the users' needs, and make them available as public saved queries in the various applications' query screens. Users can then pick date queries and enhance them to add personal criteria, such as ENTEREDBY, SUPERVISOR, etc. and save them as their own.