CODEWITHSUNDEEP
sqloracle-databasedatetime
Omnipresent
Omnipresent

Reputation: 30374

ignoring timestamp in WHERE clause when comparing to date

My table has records like these

23-MAY-11 11.40.39.000000 AM

The following query brings nothing

SELECT * 
  FROM my_table 
 WHERE tenant_pha = 'test'
   AND create_date >= TO_DATE('05/10/2011','mm/dd/yyyy')
   AND create_date <= TO_DATE('05/23/2011','mm/dd/yyyy')

However, the below query will bring data

SELECT * 
  FROM my_table 
 WHERE tenant_pha = 'test'
   AND create_date >= TO_DATE('05/10/2011','mm/dd/yyyy')
   AND create_date <= TO_DATE('05/24/2011','mm/dd/yyyy')

I think this is because create_date column is time stamp.

How can I change my query to bring the desired result ( I want to avoid doing functions on the left side columns because they will make the query long).

Upvotes: 2

Views: 9326

Answers (2)

user330315
user330315

Reputation:

You can use trunc() without problems, you only need to create a function based index.

If you create this index:

CREATE INDEX idx_trunc_date ON my_table (trunc(create_date));

then the following condition will make use of that index:

AND trunc(create_date) >= TO_DATE('05/10/2011','mm/dd/yyyy')

Upvotes: 2

Geoff
Geoff

Reputation: 9340

You are right about the timestamp. '05/23/2011' is the same as '05/23/2011 12:00 AM'.

To include the whole day I usually move my date up by a day. < '05/24/2011' will include all of 5/23.

or change to '05/23/2011 23:59:59'

Upvotes: 4

Related Questions