Home » SQL & PL/SQL » SQL & PL/SQL » without using trunc how will you write this query excluding time (oracle 10g)
without using trunc how will you write this query excluding time [message #443350] Mon, 15 February 2010 04:31 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
AND TRN.DATE BETWEEN
       TO_DATE('01-JAN-2009', 'dd-mon-yyyy') AND
       TO_DATE('31-MAR-2009', 'dd-mon-yyyy')


i should considr only dates between 1st JAN to 31st march.

if i use trunc(TRN.DATE) then its giving correct result.but if use trunc function then i think index will not work.
actually my assumption is if i use 'dd-mon-yyyy' it will not consider time ?

[Updated on: Mon, 15 February 2010 04:40] by Moderator

Report message to a moderator

Re: without using trunc how will you write this query excluding time [message #443353 is a reply to message #443350] Mon, 15 February 2010 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AND TRN.DATE BETWEEN
       TO_DATE('01-JAN-2009', 'dd-mon-yyyy') AND
       TO_DATE('01-APR-2009', 'dd-mon-yyyy')-1/86400

or
AND TRN.DATE >=
       TO_DATE('01-JAN-2009', 'dd-mon-yyyy')
 AND TRN.DATE <
       TO_DATE('01-APR-2009', 'dd-mon-yyyy')

Regards
Michel
Re: without using trunc how will you write this query excluding time [message #443355 is a reply to message #443350] Mon, 15 February 2010 04:45 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Times are always considered. What trunc does is set the time component of a date to midnight, and yes it will prevent index usage unless you have a function based index.
Likewise if you don't specify a time in a to_date the time component of that date gets set to midnight.
Your code is equivalent to:
AND TRN.DATE BETWEEN
       TO_DATE('01-JAN-2009 00:00:00', 'dd-mon-yyyy HH24:MI:SS') AND
       TO_DATE('31-MAR-2009 00:00:00', 'dd-mon-yyyy HH24:MI:SS')

So it'll exclude any dates on the 31st Mar where their time component is after midnight.
What you want is this:
AND TRN.DATE BETWEEN
       TO_DATE('01-JAN-2009 00:00:00', 'dd-mon-yyyy HH24:MI:SS') AND
       TO_DATE('31-MAR-2009 23:59:59', 'dd-mon-yyyy HH24:MI:SS')


That'll get you every record for those dates irrespective of the time.
Re: without using trunc how will you write this query excluding time [message #443356 is a reply to message #443350] Mon, 15 February 2010 04:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Create your index on TRUNC(date_column)

Previous Topic: dynamic sql statement
Next Topic: How to improve the performance of this SP?
Goto Forum:
  


Current Time: Sun Dec 04 08:25:00 CST 2016

Total time taken to generate the page: 0.20452 seconds