For some reason, when you use the keyword “Between” in your query criteria, it
acts differently w/ regard to how it includes the parameters depending whether
the parameters are date data types or not. Here’s some examples:
Ex 1: Calls.Number Between 39140 and 39145 <-- In this case,
39140 and 39145 are included in query criteria
Ex 2: Calls.Datereported between 1/1/99 and 1/5/99 <-- In this case, 1/1/99
is included, but 1/5/99 is excluded
This doesn’t make sense to me and will be confusing to my users who use the
date criteria when defining their reports. I have also tried using >= and <=
operators instead of Between with the same bad results.
I can bet you are using the timestamps and that is your problem. The
dates are interpreted as
Between 1/1/99 12:00AM And 1/5/99 12:00AM. This will give you the
results only from jan 1 to jan4.
Good Luck
Ex 2: Calls.Datereported between 1/1/99 and 1/5/99 ← In this case, 1/1/
99
is included, but 1/5/99 is excluded
Lisa:
Make sure your date values don’t have a time element. In the basic date
“between” for BusObj, the time is listed as “12:00:00 AM” which will include
the entire day for the first date value provided. If you have any time value
on the 1/5/99 date values, then they will be excluded.
If in your universe the object in question is defined as TRUNC(date_value)
then you should be okay. TRUNC(date_value) is an Oracle function that removes
the time element. Otherwise your users would have to enter the query as
Between “1/1/99 12:00:00 AM” and “1/5/99 11:59:59 PM”
The between clause includes end points. The the likely issue with your data is
that the date also includes a time. i.e. 4/2/1999 02:30:30.34. Are you working
with manufacturing data or collected from time specific events?
BusinessObjects is including a time also. Between 1/1/99 and 2/1/99 gets
translated to between 1/1/199 00:00:00.00 and 2/1/1999 00:00:00.00. There for
none of the times after the first second of 2/1/99 is between.
You can strip the time in the objects by retrieving the date, converting it to
text, and converting it back to time with an appropriate format mask. In Oracle i
believe the syntax would be:
For some reason, when you use the keyword “Between” in your query criteria, it
acts differently w/ regard to how it includes the parameters depending whether
the parameters are date data types or not. Here’s some examples:
Ex 1: Calls.Number Between 39140 and 39145 ← In this case,
39140 and 39145 are included in query criteria
Ex 2: Calls.Datereported between 1/1/99 and 1/5/99 ← In this case, 1/1/99
is included, but 1/5/99 is excluded
This doesn’t make sense to me and will be confusing to my users who use the
date criteria when defining their reports. I have also tried using >= and <=
operators instead of Between with the same bad results.
You can strip the time in the objects by retrieving the date, converting
it to
text, and converting it back to time with an appropriate format mask. In
Oracle i
believe the syntax would be:
You don’t have to do all that. Simply truncate the date field:
trunc( date_field )
HOWEVER, keep in mind that any time you apply a function to a field, you
automatically disqualify that field from being used in an index. This can
have serious performance ramifications. See the following two examples.
Assuming there is an index defined on date_field, Query 1 may use the index,
but Query 2 can not.
Example Query 1
SELECT VALUE_FIELD FROM DATA_TABLE
WHERE DATE_FIELD BETWEEN DATE_CONST1 AND DATE_CONST2
Example Query 2
SELECT VALUE_FIELD FROM DATA_TABLE
WHERE TRUNC(DATE_FIELD) BETWEEN DATE_CONST1 AND DATE_CONST2