Advanced date queries for Maximo (SQL Server)

Technote (troubleshooting)


Problem(Abstract)

This document shows examples of SQL syntax that can be added to a where clause to query based on fixed date ranges, such as "Last 7 days" or "This week". These can also be useful in Workflow where you cannot use :DATECOLUMN syntax in a condition.

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 Microsoft SQL Server:

For records with a date and time within the last seven days of the current time. Change datecolumn to the appropriate column for the table:

AND (datecolumn BETWEEN dateadd(day, -7, getdate()) AND getdate())

For records with a date within the last seven calendar days (the Convert function allows you to set the date to a mm/dd/yyyy string format, which cuts off the time component, effectively setting it to midnight):

AND (datecolumn BETWEEN dateadd(day, - 7, convert(varchar, getdate(), 101)) AND getdate())

Here are the same query conditions, but for a range of the last 30 days from the current date.

For records with a date and time within the last thirty days to the current time. Change datecolumn to the appropriate column for the table:

AND (datecolumn BETWEEN dateadd(day, -30, getdate()) AND getdate())

Records with a date within the last thirty calendar days.

AND (datecolumn BETWEEN dateadd(day, - 30, convert(varchar, getdate(), 101)) AND getdate())

You can also use this type of condition to select by a specific calendar week (Sun - Sat) instead of 7 days prior to today. The week definition is established by the command Set Datefirst. This establishes the day of the week that the week starts on. The Default is 7 for the U.S. English week starting on Sunday. The statement 'datepart(dw, getdate())' will now display a value of 1 for Sunday and 7 for Saturday.

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

AND (datepart(wk, datecolumn) = datepart(wk, getdate())) and (datepart(year, datecolumn) = datepart(year, getdate()))

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

AND ( datepart(wk, datecolumn) = datepart(wk, dateadd(wk, - 1, getdate()))) and (datepart(year, datecolumn) = datepart(year, getdate()))

A DBA or Maximo Administrator can develop useful date range queries to meet the user's needs, and make them available as public saved queries in the various application query screens. You can then pick date queries and enhance them to add personal criteria, such as ENTEREDBY, SUPERVISOR, etc. and save them as your own.

Product Alias/Synonym

Maximo

Rate this page:

(0 users)Average rating

Document information


More support for:

IBM Maximo Asset Management

Software version:

5.2, 6.2, 6.2.1, 6.2.2, 6.2.3, 6.2.4, 6.2.5, 6.2.6, 6.2.7, 6.2.8, 7.1, 7.1.1, 7.5

Operating system(s):

Windows

Reference #:

1295192

Modified date:

2008-12-11

Translate my page

Machine Translation

Content navigation