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