Query oracle Db for data between two dates

Question

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?

Littlefoot · Accepted Answer · 2022-11-30 06:56:23Z

3

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

answered Nov 30, 2022 at 6:56

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aman Over a year ago

It worked, thanks a lot! Btw in my case data was stored as varchar2 (I know it was a bad idea) so I had to do TO_TIMESTAMP(created_time, 'yyyy-MM-dd"T"HH24:mi:ss.ff3')

Littlefoot Over a year ago

You're welcome; I'm glad you made it work.

Wernfried Domscheit · Accepted Answer · 2022-11-30 07:05:29Z

2

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

edited Nov 30, 2022 at 7:05

answered Nov 30, 2022 at 6:55

Wernfried Domscheit

60.4k10 gold badges92 silver badges132 bronze badges

Collectives™ on Stack Overflow

Query oracle Db for data between two dates

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related