risky use of Oracle SYSDATE?

Post #167,527 by tablizer 8/4/04 7:45:50 PM Reply	risky use of Oracle SYSDATE? The following is a clause we found that checks to see if a record is in the specified date range. If the end-date is empty, it assumes record is in range. But, what if there is a slight difference in resulting time between the first evaluation of SYSDATE and the second? One could be evaluated at second 32.999999 and the next at second 33.000001, for example. SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE) It makes me nervious because it would be hard to recreate and isolate if it went wrong. ________________ oop.ismad.com
Post #167,529 by broomberg 8/4/04 8:15:43 PM Reply	Hmm. Order of evaluation If the left sysdate is aways evaluated before the right sysdate, a discrepency would not matter. If the reverse happens, you are SOL. And, since the right is in a parens as part of a function, my guess is that it could be evaluated 1st. You are right, this is scary usage. Change the right one to "sysdate+1" and it will NEVER be too early. Document it well.
Post #167,538 by admin 8/4/04 9:31:53 PM Reply	If only the date matters... use TRUNC(SYSDATE). Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #167,542 by broomberg 8/4/04 10:27:10 PM Reply	Still could cross over
Post #167,546 by admin 8/4/04 11:06:54 PM 8/4/04 11:10:14 PM Reply	Unless you change the logic to include a +1, as you indicated. If you use a trunc, however, you always know where you stand. Of course, you might need to add a functional index to the table as well. Personally, I might throw sysdate into a variable and use that instead, depending on where this is being used. Side note: in our system, we have a function to wrap sysdate. This lets us actually set sysdate to whatever we want for testing purposes. We're also guaranteed to always get the exact same date within the same session call. This doesn't work for sessions that aren't ended between calls, of course. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..." Edited by admin Aug. 4, 2004, 11:10:14 PM EDT Expand All History
Post #167,551 by broomberg 8/5/04 12:03:50 AM Reply	sysdate wrapper - nice touch I'm about to embark on a fullfillment tracking / automation system. Will need well controlled Oracle table updates, etc. Been YEARS since I've written anything like this. Would you happen to have a guideline doc handy for for things such at update wrappers, etc?
Post #167,552 by admin 8/5/04 12:05:40 AM 8/5/04 12:06:53 AM Reply	Guideline doc for DML wrappers: "Use them." :-) Flippancy aside, we've had reasonably good results with using object wrappers for table DML. Apparently there's such a thing as "upsert" now, which should come in handy. Depends on the situation as to what you need to do. I'd suggest the Feuerstein books if you don't have them. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..." Edited by admin Aug. 5, 2004, 12:06:53 AM EDT Expand All History
Post #168,059 by tablizer 8/7/04 5:12:05 AM 8/7/04 5:14:31 AM Reply	Re: Unless you change the logic My suggested fix was something like: \n SYSDATE BETWEEN start_date AND NVL(end_date, TO_DATE('31-DEC-2999'))\n But it would then have a Y3K problem :-) Some systems have "infinity" constants for this kind of thing. ________________ oop.ismad.com Edited by tablizer Aug. 7, 2004, 05:14:31 AM EDT Expand All History
Post #167,548 by admin 8/4/04 11:25:22 PM Reply	Another thought I'm wondering if that query is even going to use any indexes on the two columns given. var foo date := trunc(sysdate);\nselect \n from tab\n where start_date <= foo\n and (end_date is null or end_date <= foo); The above might have a better optimizer profile than the query you presented. The accuracy of this query assumes start_date and end_date are trunc()ed before insertion into the table. This is the same logically as `where start_date <= foo and (nvl(end_date, foo) <= foo)`, but probably more efficient since 'is null' is probably faster than a date compare. It's late and I'm just noodling, though. :-) If you start using things like +1, you have to be very careful to ensure you're actually getting the results you're expecting. Test all of the boundary conditions thoroughly. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."*
Post #167,549 by ChrisR 8/4/04 11:39:40 PM Reply	It would surprise me if the SYSDATE wasn't in a closure That is, it is much more efficient to grab the clock once at the start of the query and use that local variable in place of all SYSDATE's within the query. All my oracle books are at work though, so I'm just doing a WAG.
Post #167,550 by admin 8/5/04 12:01:23 AM Reply	Brief testing says this might be correct. select to_char(sysdate - sysdate, '.99999999999999') from dual; was 0 all the way out. Also: SQL> select cast(sysdate as timestamp),cast(sysdate as timestamp) from dual;\n\nCAST(SYSDATEASTIMESTAMP)\n---------------------------------------------------------------------------\nCAST(SYSDATEASTIMESTAMP)\n---------------------------------------------------------------------------\n05-AUG-04 12.00.51.000000 AM\n05-AUG-04 12.00.51.000000 AM Not exactly a rigorous test, but... I do believe that multiple calls to sysdate in a pl/sql function will return different values, however. It's certainly a performance drag. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #167,554 by ChrisR 8/5/04 12:35:23 AM Reply	FWIW SQL 2000 doesn't allow you to call GETDATE() from within functions for that reason (which can be a real PITA when that's what you actually need to do). I'd assume that functions are sequential, not atomic, in nature, so you would be faced with SYSDATE varying through a function called within a query.

Welcome to IWETHEY!