Re: Touble With Dates

Warden, Ronald MCF:EX wrote:
An easier way to deal with this is to set the NLS_DATE_FORMAT at the
beginning of your program. This requires you to alter your session and
set the NLS_DATE_FORMAT. After that all your date will be displayed in
whatever format you choose.

Note that the original poster was not *displaying* dates. He was doing
date comparisons for results selection (ie, the where clause)

Relying on NLS_DATE_FORMAT for this purpose can be dangerous practice,
because it means your SQL has different meaning depending on
the context in which the SQL is executed, eg:

select .. where SOME_DATE_COL = ?

You can't tell what that does without knowing the session
settings. Compare to:

select .. where trunc(SOME_DATE_COL) = trunc(?)

which is explicit, and consistent regardless session settings.

If you have absolute, guaranteed control over session context
for the lifetime of your code, great. Otherwise, the latter
is generally better practice.


Every function adds execution cost to your script. If you want to
compare some dates to in the database to to-day, then I would grab one
of the PERL date modules, set a variable at the start of you program
with to-day's date, setup a placeholder in your prepare statement and
then drop the variable into the execute statement. This has the added
advantage of not having to run an extra query, to get sysdate, every
time your loop executes.

Perhaps, but what *how much* something costs matters too
(and trunc and sysdate are pretty cheap)

"Premature optimization is the root of all evil" -- Donald Knuth

Mark
.

Relevant Pages

  • Re: Is there a way to speed up Index Fast Full Scan
    ... when processing those slow SQL statements. ... session by checking V$SESSION. ... execute one of the slow ... then have the session execute the slow SQL statements. ...
    (comp.databases.oracle.server)
  • Re: Is there a way to speed up Index Fast Full Scan
    ... when processing those slow SQL statements. ... session by checking V$SESSION. ... execute one of the slow ... then have the session execute the slow SQL statements. ...
    (comp.databases.oracle.server)
  • Re: HTTPContext Session and worker thread (Fire and Forget)
    ... To kick of the job you insert record into tblJobs with MessageIn having ... Updates Session if job ... not execute job cause it was grabbed by other pooling thread between step 1 ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: VBScript with Sessionid and RDP
    ... execute the following commands at a Command Prompt in order to examine its ... If you get no output then there you must run this command so that you can ... Remote Desktop Session), only RD Session instance is killed. ... message - how about starting the batch file with "echo on"? ...
    (microsoft.public.scripting.vbscript)
  • Question about import and sys.path
    ... When a menu option is selected, I execute the program like this - ... at the beginning of the session. ... Does execfile create a .pyc file from a .py file, ... Are there any other implications I should know about? ...
    (comp.lang.python)