ARS Version 7.5 patch 003. SunOS 5.10. Oracle ver 10.2.0.3.0
I've got a form with a date field on it. Note that that's a date
field (which at the database level stores a juilian value, like
"2455463"), and not a date/time field (which stores an epoch value,
like "1285214400")
Now, if I try to do a query like 'DateField' < $DATE$ or 'DateField' <
$TIMESTAMP$, that works fine.
If, however, I try to do a query like 'DateField' < ($DATE$ + (30 *
86400)), which *should* mean return all records where the date in the
field is no later than 30 days from now, I get incorrect results. I
get all records with non-null values, whether or not the date is
before or after 30 days from now.
I tried all 4 sensible variations of the query, employing combinations
using $TIMESTAMP$ or $DATE$ and (30 * 86400) or (30) with the same
results.
If I hardcode the date, i.e. 'DateField' < ("8/1/2011" + (30)), only
then are the results correct.
Looking at the SQL log, it would appear that any attempt to perform a
query using a date calculation involving a keyword is translated into
an SQL statement that uses an epoch value, regardless of the fact that
the field that the comparison is being made against is a julian date,
thus the incorrect results.
This appears to be true with queries in the user tool advanced search
bar as well as in qualifications on filters and escalations.
I need to be able to have an escalation that has a qualification along
the lines of 'DateField' < ($DATE$ + 30).
The obvious solution is to replace the date field with a date/time
field, but for a variety of reasons, I would like that to be my last
resort.
Anybody know how to make the qualification behave properly?
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
