RE: Touble With Dates
- From: imharisa@xxxxxxxxxx (Ian Harisay)
- Date: Fri, 26 May 2006 10:13:27 -0600
I would only add that I believe the to_char method is more expensive
because you are taking temporal data, converting it to a string
representation and then doing a string comparison. Really not a big
deal but this can be quite expensive with large data sets. Then again
with a large data set (assuming shipdate is indexed) I would write it
out like
where shipdate >= trunc(sysdate-21)
and shipdate < trunc(sysdate-20)
NOTE: it is generally a bad idea to use a function on left side of =.
This will cause a full table scan.
-----Original Message-----
From: mark [mailto:mark@xxxxxxxxxxxxx]
Sent: Thursday, May 25, 2006 1: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
.
- Prev by Date: RE: Problem with DBI::Multiplex
- Next by Date: Named pipe
- Previous by thread: Re: Touble With Dates
- Next by thread: DBI/DBD::Oracle and Oracle 10g
- Index(es):
Relevant Pages
|
|
