Oracle SQL : timestamps in where clause

Question

I need to look up rows within a particular time frame.

select * 
from TableA 
where startdate >= '12-01-2012 21:24:00' 
  and startdate <= '12-01-2012 21:25:33'

I.e.: I need to look up rows with timestamp precision of SECONDS. How do I achieve this?

FYI: The startdate column is of type TIMESTAMP.

What is the schema of your table? – simchona Jan 13 '12 at 18:34 — simchona, Jan 13 '12 at 18:34
What happens if you use the query in the question? – Jan 13 '12 at 18:36 — , Jan 13 '12 at 18:36
The field in question is "TIMESTAMP", – sid Jan 13 '12 at 18:37 — sid, Jan 13 '12 at 18:37

score 156 · Accepted Answer · edited May 13 '19 at 13:32

156

to_timestamp()

You need to use to_timestamp() to convert your string to a proper timestamp value:

to_timestamp('12-01-2012 21:24:00', 'dd-mm-yyyy hh24:mi:ss')

to_date()

If your column is of type DATE (which also supports seconds), you need to use to_date()

to_date('12-01-2012 21:24:00', 'dd-mm-yyyy hh24:mi:ss')

Example

To get this into a where condition use the following:

select * 
from TableA 
where startdate >= to_timestamp('12-01-2012 21:24:00', 'dd-mm-yyyy hh24:mi:ss')
  and startdate <= to_timestamp('12-01-2012 21:25:33', 'dd-mm-yyyy hh24:mi:ss')

Note

You never need to use to_timestamp() on a column that is of type timestamp.

edited May 13 '19 at 13:32

StackzOfZtuff

2,534
1
28
25

answered Jan 13 '12 at 18:37

I get an error: "ORA-01830: date format picture ends before converting entire input string" here is my query: "select * from TableA where To_Timestamp(startdate, 'DD-MM-YYYY HH24:MI:SS') > To_Timestamp('12-01-2012 21:24:00', 'DD-MM-YYYY HH24:MI:SS');" – sid Jan 13 '12 at 18:44
The value in the field is "12-JAN-12 09.24.06.355000000 PM" & its a TimeStamp field in oracle db – sid Jan 13 '12 at 18:46
@user1095057: you don't not need to apply `to_timestamp()` on your column but on your input values. See my edit. – Jan 13 '12 at 19:06

score 15 · Answer 2 · answered Jan 04 '19 at 20:34

15

For everyone coming to this thread with fractional seconds in your timestamp use:

to_timestamp('2018-11-03 12:35:20.419000', 'YYYY-MM-DD HH24:MI:SS.FF')

answered Jan 04 '19 at 20:34

jyapx

2,009
2
15
18

Oracle SQL : timestamps in where clause

2 Answers2

to_timestamp()

to_date()

Example

Note

Linked