Maximo Open Forum

Hi @zena goring!

This consultation is for the week before the current one:

SELECT wonum, reportdate
FROM workorder
WHERE reportdate BETWEEN
    (CURRENT_DATE - (DAYOFWEEK_ISO(CURRENT_DATE) + 6) DAYS)
    AND (CURRENT_DATE - DAYOFWEEK_ISO(CURRENT_DATE) DAYS + 1 DAY) order by reportdate;

This query is for the current week:

SELECT wonum, reportdate
FROM workorder
WHERE reportdate BETWEEN 
    (CURRENT_DATE - (DAYOFWEEK_ISO(CURRENT_DATE) - 1) DAYS)
   AND (CURRENT_DATE + (7 - DAYOFWEEK_ISO(CURRENT_DATE)) DAYS) order by reportdate;

Regards!

Hi Martin,

Thank You for that, i have tried that directly within the Where Clause and it brings back error "The query contains an invalid condition. Modify the query.

@zena goring Which database are you using? Oracle, SQL Server, or DB2?

For SQL Server (though there might be a prettier way to do it. . .)

SELECT wonum, reportdate
FROM workorder
WHERE reportdate BETWEEN (DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)) and (DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)  + 0.999999999999999)
order by reportdate;

Note: apparently SQL Server rounds to 3 milliseconds; that's why this ends up at 23:59:59.997

Hi Travis, Thank You for your response but unfortunately that also returns "The query contains an invalid condition"

Zena, what database do you use?

Regards!