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. The day I knew tech journalism was dead

    One area I've loved since my AOL days was getting better information on computers and tech gadgets from online sources. Sure the articles in PC Magazine and Macworld were great, but the time gap between print and online media was already evident back then. The online reviews were typically written …

  2. Geek Household MCU Movie Viewing Order

    To say our household is a Marvel household, you only have to look at the serious discussions we have around the dinner table.1 The most recent discussion was:

    What it would take to see see the entire Marvel Cinematic Universe in the fewest movies possible?

    We realized that before …

  3. 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 …

  4. Using Keyboard Maestro to drive Marked 2

    My writing tools are varied based on where/how I'm writing - laptop vs iPad vs iPhone. But the final review process almost always ends up on my Mac in Marked 2. Marked 2 is a peculiarly wonderful app in that it doesn't directly create anything. Marked 2 is just for …

  5. 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 …