Using TO_DATE() with AM/PM Formatting

Question

I am trying to select some dates from a table where the format of the dates is like this:

14-APR-14 10.35.00.0000000000 AM
01-NOV-16 02.43.00.0000000000 PM

Note that the dates can be either AM or PM, but when I try to do a simple SELECT from the table such as:

SELECT * FROM MyTable
WHERE TO_DATE(MyDate, 'DD-MON-YYYY HH:MI:SS AM') > '31-DEC-2016 08:00:00 AM';

I get the error:

ORA-01855: AM/A.M. or PM/P.M. required

I've been trying to get this work for some time but with no luck. Any help here would be appreciated.

Answer 1

From your comment:

It's actually a timestamp; not a string. Timestamp(6) to be precise

You can just use a TIMESTAMP literal:

SELECT *
FROM   MyTable
WHERE  MyDate > TIMESTAMP '2016-12-31 08:00:00';

Answer 2

Several problems.

Your inputs are obviously strings, since they have ten decimal places and timestamps in Oracle have at most 9. Then, strings with fractions of a second can't be converted to a date with to_date - you need to use to_timestamp or else you need to remove all the fractional parts. In the solution below I only remove the last (the tenth) decimal, since you may have non-zero fractional parts in the table - although not in the sample you posted.

Then, your format mask has yyyy but your inputs have only two digits for the year (which probably means 93 means 1993 and not 2093, so the correct thing to use would be rr rather than yy). And you use : in the format mask where your inputs use .

Finally, don't even compare dates in string format: in string comparisons, 01-JAN-2015 is before 20-NOV-2013.

You probably want something like this:

select mydate
from   (
         select '14-APR-14 10.35.00.0000000000 AM' as mydate from dual
         union all
         select '01-NOV-16 02.43.00.0000000000 PM' from dual
       ) mytable
where to_timestamp(substr(mydate, 1, 28) || substr(mydate, -3), 'dd-MON-rr hh.mi.ss.ff AM')
         > to_timestamp('31-DEC-2016 08:00:00 AM', 'dd-MON-yyyy hh:mi:ss AM');

This query compiles correctly, and it produces no rows in the output (for obvious reasons).

NOTE: In a comment you (the OP) say the mydate field is a timestamp(6) datatype. Hard to believe (you show ten decimal places), but if indeed it is a timestamp or date, then you don't need to wrap it within any to_timestamp or to_date function, it should stand alone in the left-hand side of the inequality.