Query oracle Db for data between two dates

Query oracle Db for data between two dates

Problem Description:

Trying to query an oracle db table having date in format: 2022-06-22T12:25:06.087 (LocalDateTime.now().toString()). Column type for created_time is varchar2.
Trying to query for data between two dates. I have tried the following but it results in error "date format not recognized":

select * from MY_TABLE
where to_date(created_time, 'yyyy-MM-ddTHH:mm:ss.SSS') 
between to_date('2022-07-03T10:15:06.091', 'yyyy-MM-ddTHH:mm:ss.SSS') 
and to_date('2022-07-03T10:15:06.091', 'yyyy-MM-ddTHH:mm:ss.SSS');

Can anyone help me correct this query?

Solution – 1

I guess column created_time is of data type DATE or TIMESTAMP. Never call TO_DATE() or TO_TIMESTAMP() to a values which is already a DATE

The DATE data type does not support fractional seconds, use TIMESTAMP instead. Format literals have to be enclosed by double quotes.

Format HH is the hour in 12-hour format. I assume you need 24-hour format, which is HH24. mm (or MM) is the Month, for Minute use MI. Format identifiers are not case-sensitive, so SSS is also wrong.

Try this one:

select * 
where created_time
   between TO_TIMESTAMP('2022-07-03T10:15:06.091', 'yyyy-MM-dd"T"HH24:MI:ss.ff3') 
   and TO_TIMESTAMP('2022-07-03T10:15:06.091', 'yyyy-MM-dd"T"HH24:MI:ss.ff3');

Solution – 2

Format you used looks like data (in CREATED_TIME column) is stored as a timestamp. If that’s so, you shouldn’t convert it to another datatype (you chose TO_DATE function) but leave it as is. If you stored data as a string (that’s usually a huge mistake), then apply the same to_timestamp function with the same format model as the one in between clause.

Apart from that, format model for minutes is mi (not mm; that’s month), while fractional seconds is ff3 (not sss).

  FROM my_table
 WHERE created_time 
   BETWEEN TO_TIMESTAMP ('2022-07-03T10:15:06.091', 'yyyy-MM-dd"T"HH24:mi:ss.ff3')
       AND TO_TIMESTAMP ('2022-07-03T10:15:06.091', 'yyyy-MM-dd"T"HH24:mi:ss.ff3');
Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.