IBM Support

Advanced date queries for Maximo (Oracle)

Troubleshooting


Problem

Users often want to query records based on fixed date ranges, such as "Last 7 days" or "This week". This shows examples of SQL syntax that can be added to a where clause to do this.

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.

[{"Product":{"code":"SSLKT6","label":"IBM Maximo Asset Management"},"Business Unit":{"code":"BU055","label":"Cognitive Applications"},"Component":"Not Applicable","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB02","label":"AI Applications"}},{"Product":{"code":"SSLKTY","label":"Maximo Asset Management for IT"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"","label":"All"}],"Version":"","Edition":"","Line of Business":{"code":"LOB02","label":"AI Applications"}},{"Product":{"code":"SSWK4A","label":"Maximo Asset Management Essentials"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":null,"Platform":[{"code":"","label":"All"}],"Version":"","Edition":"","Line of Business":{"code":"LOB02","label":"AI Applications"}}]

Historical Number

M04460

Product Synonym

MAXIMO

Document Information

Modified date:
17 June 2018

UID

swg21262277