CODEWITHSUNDEEP
sqloracle-databaseselecttimestampwhere-clause
Kairan
Kairan

Reputation: 5542

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

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

Upvotes: 4

Views: 56782

Answers (1)

Dave Costa
Dave Costa

Reputation: 48111

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.)

Upvotes: 9

Related Questions