Setting a DateTime for testing SQL statements

One of the tenets I teach about reporting in Maximo is to look for the exceptions in the data, not the data itself. Out of the box, Maximo has quite a few key reports included, one of them is the WO List report (wotrack.rptdesign). That report lists every work order contained in the current search criteria. And if that search criteria is empty, you'll get every open work order.

For the company I work for, we've looked at our work orders based on the work week and reporting period. For us, our work week is from Saturday through Friday. 1 So when we report on upcoming work, it was natural for us to look at work orders only from the next Saturday to the Friday after that. So if today is 2018-10-02, then we'd want to see a date range of Sat (2018-10-06) through Fri (2018-10-12).

When testing date queries, most beginners use variation of getdate(). So if we test a SQL query like what is the first day of next month

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

That kind of query can be tested on any day of the month - it works or it doesn't.

But then there are times when we want to have a query find a specific day of the week, like this coming Saturday. That requires some more complex query statements and we need to check to see if the calendar date rolls over or not. For example, if we wanted to find the the date of this coming Saturday.

select dateadd(wk, datediff(wk, -8, getdate() ), -2)

While the statement works, the question is does it work for each day of the week?

We can temporarily set a date value and then be able to change the date to see how the query rolls through the rest of the week.

declare @calendardate as datetime
set @calenderdate = '10/6/2018'
select dateadd(wk, datediff(wk, -8, @calenderdate), -2)
select dateadd(wk, datediff(wk, -15, @calenderdate), -2)

Now I have a way to test date/time queries to see how they change through the week. But this method means I need to manually iterate the dates over the next 7 days. I'd prefer to have a method to roll through the next 7 days and output the results. In SQL you can iterate over a data set. So to test the date modification dateadd(wk, datediff(wk, -8, getdate() ), -2), we can use the following query statement.

declare @calendardate as datetime
set @calendardate = {ts ' 2018-10-01 12:00:00.000'}

while (select @calendardate) < getdate() + 7  
    set @calendardate = dateadd(d, 1, @calendardate)
    select datename(dw, @calendardate), @calendardate, 
    dateadd(wk, datediff(wk, -8, @calendardate), -2)
    if (select @calendardate) >   getdate() + 7

Now I can change 1 line out of this statement in the future to test other rolling date queries.

  1. Look for more on why in an upcoming post.  

Previous posts:

  1. Replicating Spare Part Lists with Maximo BIRT & MIF

    One of the complaints of using a software platform for a long time is you eventually say,

    I wish we had this feature when we got started.

    Right now that feature is Asset Templates. I'm starting to load several hundred assets into Maximo due a large capital project at one …

  2. Incorporating Location Hierarchy on WO Print

    My company has recently been challenged with how we're using Maximo. On one front we're deploying Maximo to new sites that are transitioning off of another EAM platform. On the other front we have a facility that's tripling the size of the installed asset count. Both sites are dealing with …

  3. Setting PM to 1st Day of the Month

    First off, shout out to Bruno Portaluri (@bportaluri) for this tip. He summarized an answer to a question I had just received a few days earlier from one of my Planners. This Planner asked if we could schedule a PM to come out the first Friday of each month. At …

  4. Using Conditions for Tab Hiding

    My company recently migrated from Maximo 7.5 to 7.6. 1 One of the challenges an administrator has with a new version of Maximo is adjusting screens that come over as part of the upgrade. In our case we got a couple of new tabs because of new functionality …

  5. Add Conditional Security to prevent WO Cancellation

    A recent tweet from IBM on Maximo was technote about how a work order could be cancelled, even if materials were charged against the WO and recorded in the Actuals tab. For most long time Maximo users, the immediate reaction was, 'Pfffttt....that isn't possible.' IBM documented with 2 scenarios …