One of the things I try to re-enforce with Maximo users is striving to re-use their work, especially queries, as much as possible. A common task in Maximo is to find and sort information related to dates – order date, approval date, status date, etc. A common problem users come back to me is when they make a really useful query, but find that the query isn’t working as expected because the query was hard coded with a date range. Now when they try to use that query as part of their normal workflow, or in a QBR report, the information comes back less than useful. This post will go over some date related SQL statements that will make your Maximo queries even more powerful.
One of the common tasks users need to do is report, or list, a set of records based on certain date range, say the last 24h. A common frustration I see with users is trying to get the query to be just the past 24h, like this:
2012–01–01 00:00:00.000 to 2012–01–01 23:59:59.999.
But you know what is just as good? Adding 0.001 seconds to that query and run the date range from
2012–01–01 00:00:00.000 to 2012–01–02 00:00:00.000
Running the query from 12:00am to 12:00am gets you everything you’d want from 12:00am to 11:59pm. In the event you have a transaction that lands exactly on 00:00:00.000, go buy a lottery ticket, you are just that lucky. Maximo allows the use of several SQL functions, including the use of getdate(), dateadd, convert, and datediff. Using these date modifiers allows queries to now have rolling date ranges that will change as the calendar moves on. Here are some examples of rolling date queries you can use in Maximo.
Current Date to Midnight
Change current date to midnight of current date:
dateadd(day, 0, convert(varchar, getdate(), 101))
To change the day the query will be set to midnight to, just change the integer after the ‘day’ field. For example, to return a date of midnight yesterday, the date query would be:
dateadd(day, −1, convert(varchar, getdate(), 101))
And to get 7 days ago, just do:
dateadd(day, −7, convert(varchar, getdate(), 101))
Assuming today is 8/29/12, then the query above would return ‘2012–08–22 00:00:00.000’.
Current Date/Time to Just Date
Change the current date/time stamp to just a date format of mm/dd/yy
convert(varchar, getdate(), 101)
Set First Day of Previous Month
Change the current date/time to yyyy-mm-dd 00:00:00.000 and have the date be the first day of the previous month. So if the current date is 4/21/11, this SQL statement would return 3/1/11.
dateadd(month,datediff(month,1,getdate())–1,0)
Set First Day of Current Month
Change the current date/time to yyyy-mm-dd 00:00:00.000 and have the date be the first day of the previous month. So if the current date is 4/21/11, this SQL statement would return 4/1/11.
dateadd(month,datediff(month,1,getdate())+0,0)
Just like the adjustment in the midnight date query, adjust the integer at the end of the query (….th,1,getdate())+1,0) will shift what first of the month that will be returned, this case it would return 5/1/11 based on the previous criteria.
Example query
The following query is used to show all the active purchase orders for a plant site in the last 24h.
(orderdate <= dateadd(day, 1, convert(varchar, getdate(), 101)) and historyflag = 0 and siteid = (select defsite from maxuser where userid= :user ) and orderdate >= dateadd(day, –1, convert(varchar, getdate(), 101)) )
So if this query is run at 8:43am on 6/12/12, the query will look between the following dates:
2012–06–11 00:00:00.000 to 2012–06–13 00:00:00.000
Now for some users this will be a little unsettling because it looks like the query is actually searching over 2 days, not just the last 24h. But remember, we’re treating 2012–06–13 00:00:00.000 = 2012–06–12 23:59:59.999. A lot of users will use a shortcut and use the following query for the last 24h by using getdate() and getdate()–1.
(orderdate <= getdate() and historyflag = 0 and siteid = (select defsite from maxuser where userid= :user ) and orderdate >= getdate()–1 )
So what happens if you run this report at 3:17pm for a quick report for the plant manager? The second query will definitely be fixed on just the last 24h, but it will also miss any PO created before 3:17pm yesterday. That’s why getting users to look at date filters based on midnight (00:00:00.000) to midnight is such a critical culture change.
References
Here are some references for additional details related to some of the funcitons used in the query statements.
MSDN SQL help for GETDATE
MSDN SQL help for DATEADD
MSDN SQL help for DATEDIFF
MSDN SQL help for CAST & CONVERT
Bonus SQL Statement
Use the following query to set the date to be just before midnight today, which will yield a 23:59:59.999 time stamp.
select dateadd(millisecond,–1, (dateadd(day, 1, convert(varchar, getdate(), 101))))