RE: Touble With Dates
- From: Ronald.Warden@xxxxxxxxx (Ronald MCF:EX Warden)
- Date: Thu, 25 May 2006 13:38:57 -0700
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.
An example is:
SQL> alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select to_char(sysdate) from dual;
TO_CHAR(SYSDATE)
--------------------
25-MAY-2006 13:29:06
SQL> alter session set NLS_DATE_FORMAT ='MMDDYY';
Session altered.
SQL> select to_char(sysdate) from dual;
TO_CHA
------
052506
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.
Hope this helps.
Ron
-----Original Message-----
From: Hedstrom, Charlie [mailto:Charlie.Hedstrom@xxxxxxxxxx]
Sent: May 25, 2006 1:00 PM
To: mark; Joseph Lamb
Cc: dbi-users@xxxxxxxx
Subject: RE: Touble With Dates
But who would want to have a query only match a down-to-the-second
timestamp exactly 21 days ago from the time of the query?
If the intent of the query is to find all entries made anytime during
the day 21 days ago, you will need the trunk function.
-Charlie
-----Original Message-----
From: mark [mailto:mark@xxxxxxxxxxxxx]
Sent: Thursday, May 25, 2006 3:40 PM
To: Joseph Lamb
Cc: dbi-users@xxxxxxxx
Subject: Re: Touble With Dates
Cliff Nadler wrote:
Try the following instead:
trunc(SHIPDATE) = trunc(SYSDATE)-21
Oracle's date fields (and especially SYSDATE) are date + time. The
TRUNC will trucate the value to the date only (actually midnight) so
they can compare correctly.
It would be curious if trunc works, but there is nothing
wrong with the way Joseph was doing it -- both where clauses should
yield exactly the same results. For example:
SQL>select * from x_ship
where to_char(shipdate, 'MMDDYY') = to_char(sysdate-21, 'MMDDYY')
;
SHIPDATE
-------------------
2006-05-04 12:26:59
SQL> select * from x_ship
where trunc(shipdate) = trunc(sysdate-21);
SHIPDATE
-------------------
2006-05-04 12:26:59
I don't see any reason for your problem. You should turn on tracing and
run the query from sqlplus and from DBI and see what the db is actually
executing in each case.
I would add however that using the trunc is, IMO, preferred, because the
1) the to_char character conversion serves no purpose, and 2) it muddies
the intention. You *want* to compare *dates*, so you should compare
dates.
Mark
.
- Follow-Ups:
- Re: Touble With Dates
- From: Mark
- Re: Touble With Dates
- Prev by Date: DBI/DBD::Oracle and Oracle 10g
- Next by Date: RE: DBI/DBD::Oracle and Oracle 10g
- Previous by thread: Re: Touble With Dates
- Next by thread: Re: Touble With Dates
- Index(es):
Relevant Pages
|
|
