One of the common tasks a Maximo user is asked to do is find information based on a date range. Overtime some of these requests become repetitive enough in nature that it makes sense to create queries that can automatically update upon use. Listed below are some common date/time SQL query statements that can be used to help make standard search queries automatically roll with date changes.

In all of the SQL statements below¹, the results assume that the current date/time would be: 2013-01-11 10:30:22.713.

Almost all of the heavy lifting shown below is using a the DATEADD² function and a date/time conversion with:

convert(varchar, getdate(), 101)

That conversion changes the current date/time value to just a date value. When this occurs the subsequest adjustments made by DATEADD function make the assumption that the date is no longer just 2013-01-11 but is 2013-01-11 00:00:00.000.

Searching between dates

One of the common search items is to find records based on date ranges. To set up the example, using the Advanced Search function in WO Tracking, a user goes in and searches for all work orders reported between today and last Saturday (1/5). The goal is to show all the work orders reported in the last 7d. If the user enters:

Reported Date From: 1/6/13
Reported Date To: 1/11/13

The resulting query Where Clause would look something like this:

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and reportdate <= {ts '2013-01-11 23:59:59.000' } and historyflag = 0 and siteid = 'BEDFORD' and istask = 0 and reportdate >= {ts '2013-01-06 00:00:00.000' })

Note how the To Date was automatically adjusted to be the end of the day at 23:59:59. While this is the most accurate SQL statement to return the desired results, how likely is it that a work order was reported in the 1 second before midnight? So an alternate search Where Clause could be:

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and reportdate < {ts '2013-01-12 00:00:00.000' } and historyflag = 0 and siteid = 'BEDFORD' and istask = 0 and reportdate >= {ts '2013-01-06 00:00:00.000' })

Unless your results are absolutely critical to be in specific date range, doing query ranges from midnight to midnight are far more easily managed. Most of the examples below will be based on this philosphy. (Note: one minor difference between the two queries is the latter date range selection uses '<', not the '<=' as returned from the Advanced Search version)

Getting Date/Time for 00:00:00.000

As previously stated, a simple way to conduct date range searches is to go from midnight to midnight. But how do you get a date value and change the time to 00:00:00.000. The following statement:

dateadd(day, +0, convert(varchar, getdate(), 101))

This will return the following value: 2013-01-11 00:00:00.000

To adjust the date that is returned you modify the +0 value. A postive will return 'x' number of future days from today and a negative will return 'x' number of past days from today. The following statement:

dateadd(day, -7, convert(varchar, getdate(), 101))

Will return the following result: 2013-01-04 00:00:00.000

So to update the search query previously listed, to show work orders reported in the last 7d, would look like this:

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and reportdate < dateadd(day, +1, convert(varchar, getdate(), 101)) and historyflag = 0 and siteid = 'BEDFORD' and istask = 0 and reportdate >= dateadd(day, -6, convert(varchar, getdate(), 101)))

First Day of the Month

Similiar to finding items in a date range, there are requests to find everything in a given month. Using the same analogy of midnight to midnight, users should search from 1st day of the month to the 1st day of the month. The following statement will result in the first day of the current month:

dateadd(month,datediff(month,1,getdate())+0,0)

This statement will return a result of: 2013-01-01 00:00:00.000

To adjust the date return, for example for 1st day of last month, change the +0 to -1, as shown below:

dateadd(month,datediff(month,1,getdate())-1,0)

This will return the value of: 2012-12-01 00:00:00.000

This is especially useful when dealing with date range queries in Decemeber and January where month and year rollovers can cause problems with other types of monthly summmary queries. The following query would show all the work orders in the previous month [Dec 2012]:

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and reportdate < dateadd(month,datediff(month,1,getdate())+0,0) and historyflag = 0 and siteid = 'BEDFORD' and istask = 0 and reportdate >= {dateadd(month,datediff(month,1,getdate())-1,0) )

But I have to have 11:59pm

There are times where a query must be validated or set to ensure it is getting the desired date range, similar to the way the original query from Advanced Search works. The following query will result in a value just before midnight of the current date:

select dateadd(second, -1, (dateadd(day, +1, convert(varchar, getdate(), 101))))

This will return the following result: 2013-01-11 23:59:59.000

This query is basically doing two dateadd functions. The first one gets the midnight date for tomorrow and then subtracts 1 second, giving the result of 23:59:59.000 for today. So to get the last second before midnight, in the previous month, the statement would be:

dateadd(second, -1, (dateadd(month,datediff(month,1,getdate())+0,0)))

Using the 11:59pm date query would result in a Where Clause similar to the original one generated by the Advanced Search:

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and reportdate <= dateadd(second, -1, (dateadd(month,datediff(month,1,getdate())+0,0))) and historyflag = 0 and siteid = 'BEDFORD' and istask = 0 and reportdate >= dateadd(month,datediff(month,1,getdate())-1,0) )

This would give you all the work orders generated last month that are still open.