Comparing Date field with specific datetime in oracle

Question

I have a table with a Date field time_process like '1/11/2021 5:10:16 AM'.

I want to select records has time_process >= '1/11/2021 05:00:00 AM' But when I query:

select * from DATA_RUN where time_process >= '1/11/2021 05:00:00 AM'

Has error:

[1]: ORA-01843: not a valid month

How can I compare this Date field with specific datetime?

Answer 1

You should use to_date to convert string to date as follows:

select * from DATA_RUN 
   where time_process >= To_date('01/11/2021 05:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM')

Answer 2

If this is a DATE datatype column, don't compare it to a string (because, this: '1/11/2021 05:00:00 AM' is a string, not a date).

So:

select * 
from data_run
where time_process >= to_date('11.01.2021 05:00:00', 'dd.mm.yyyy hh24:mi:ss');

Answer 3

Option 1 - Date and Interval literals:

SELECT *
FROM   DATA_RUN
WHERE  time_process >= DATE '2021-01-11' + INTERVAL '05:00:00' HOUR TO SECOND

Or, since you don't have any minutes or seconds, then you could use:

WHERE  time_process >= DATE '2021-01-11' + INTERVAL '5' HOUR

Option 2 - Timestamp literal:

SELECT *
FROM   DATA_RUN
WHERE  time_process >= TIMESTAMP '2021-01-11 05:00:00'

Option 3 - Convert string using TO_DATE function:

SELECT *
FROM   DATA_RUN
WHERE  time_process >= TO_DATE( '1/11/2021 05:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM' )

---

As an aside,

I have a table with a Date field time_process like '1/11/2021 5:10:16 AM'

A DATE column has no format as it is stored internally in a binary format consisting of 7-bytes (century, year-of-century, month, day, hour, minute and second). Formatting the value of a DATE is left up to the user interface; for SQL/Plus and SQL Developer (among others), this is determined by the NLS_DATE_FORMAT session parameter but each user can set their own values in each of their sessions at any time so you cannot assume that there will be a consistent format to a DATE column.