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
