Shouldn't the qualification be 'DateField' < ($DATE$ + 30)
I have seen that problem where $DATE$ value gets assigned a epoch value instead of Julian. I haven't really bothered to find out if that's a bug or as designed as I didn't really have a show stopper when I had that problem.. In your case you do. So why don't you use this as a workaround.. Set $DATE$ to a character field.. This would set it to the correct string without the time part of it.. Then use this string and construct the qualification in another character field and use the EXTERNAL keyword and this field where the qualification is to run your escalation.. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of ARS Bob Sent: Friday, September 24, 2010 2:37 PM To: arslist@ARSLIST.ORG Subject: improper behavior for query 'DateField' < ($DATE$ + (30 *86400)) 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"
