Maximo Open Forum

 View Only

 Date query help

  • Reporting
David Pattengale's profile image
David Pattengale posted 02-03-2022 14:25
Hi everyone, I have a query I created to pull records whose scheduled date is yesterday. The query runs, but I get records from other nearby dates, so I have something done incorrectly. Here is the date portion of the query:

and ((schedstart between current date - 1 days and current date)

 Have tried using = instead of between, tried other expressions as well, like getdate, datediff, etc. I am obviously missing something.  I am thinking perhaps I need to go off of the time instead. Can anyone help?
Jason Verly's profile image
Jason Verly posted 02-03-2022 18:09
Are you on SQL Server, Oracle or DB2?
Jason Verly's profile image
Jason Verly posted 02-04-2022 09:27
I'll give an example for SQL Server, but this can easily translate to Oracle or DB2. You were on the right track with trying to find a record inside a date range. The method I use in Maximo would be written as:

... and (schedstart >= dateadd(d,-1,datediff(d,-0,getdate())) and schedstart < getdate())​

The first part of the query will find all records with a "Sched Start" greater than or equal to midnight of the previous day. So for todays date of 2022-02-04, the selection for

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

will return 2022-02-03 00:00:00.000 (midnight yesterday). Then the other half of the query will find any records less than the current date/time. 

Hope this helps. 

Jason Uppenborn's profile image
Jason Uppenborn posted 02-04-2022 11:01
The Oracle version of Jason Verly's answer is

schedstart >= trunc(sysdate-1) and schedstart < trunc(sysdate)

You might notice we both used >= and <. between and is like saying >= and <= which can never be 100% accurate because you can not supply infinite 9s to 23:59:59.999... to not miss the some of that day and we don't want to include even 00:00:00.000... of the next day. It's a simple but infinite difference.
Jason Uppenborn's profile image
Jason Uppenborn posted 02-04-2022 11:24
Based on your supplied condition, it looks like you're on DB2. DB2's current date will literally only return the date - no time portion. And you can add or subtract days without using the keyword days. So, this query should work.

and (schedstart >= current date - 1 and schedstart < current date)

Also, see my Oracle answer for why to not use between when dealing with dates.

Related Content