Query oracle Db for data between two dates-CodePudding

Trying to query an oracle db table having date in format: 2022-06-22T12:25:06.087 (LocalDateTime.now().toString()). Column type used for this table 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?

CodePudding user response：

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

SELECT *
  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');

CodePudding user response：

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 * 
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');