How to select from SQL table WHERE a TIMESTAMP is a specific Date

Question

Im trying to do a query where a TIMESTAMP field is = to a specific date but my query is not working:

The field is type TIMESTAMP(6) which I have only ever worked with DATE / DATETIME fields before. Here is example of a value stored here: 04-OCT-13 12.29.53.000000000 PM

Here is my SELECT statement:

SELECT * FROM SomeTable WHERE timestampField = TO_DATE('2013-10-04','yyyy-mm-dd')

I am retrieving no results and I am assuming it has to do with the fact that its not matching the TIME portion of the timestamp

Answer 1

If you want every record that occurs on a given day then this should work:

SELECT * FROM SomeTable
  WHERE timestampField >= TO_TIMESTAMP( '2013-03-04', 'yyyy-mm-dd' )
    AND timestampField < TO_TIMESTAMP( '2013-03-05', 'yyyy-mm-dd')

That will be likely to take advantage of an index on timestampField if it exists. Another way would be:

SELECT * FROM SomeTable
  WHERE TRUNC(timestampField) = TO_DATE( '2013-03-04', 'yyyy-mm-dd' )

in which case you may want a function-based index on TRUNC(timestampField).

(Note that TRUNC applied to a TIMESTAMP returns a DATE.)