It would surprise me if the SYSDATE wasn't in a closure

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!