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.

You are missing fractional seconds in the format string. Is the date really stored as a string? — Gordon Linoff, Dec 12 '16 at 21:37
In Oracle, the DATE datatype does not store fractions of a second. That is either stored as a timestamp or as a string (the latter being a very poor choice). Please check the actual data type in the table, not what you think it may be. Then: Comparing dates as strings is always wrong; in string comparison, 01-JAN-2015 .... is before 20-NOV-2013. — , Dec 12 '16 at 21:41
Actually, those are obviously strings, not timestamps - because they have TEN decimal places after the decimal point. The maximum in Oracle is 9, so you will need to perform at least some string manipulation first, before applying any to_timestamp or to_date function. — , Dec 12 '16 at 21:49
It's actually a timestamp; not a string. Timestamp(6) to be precise. — BlargZombie, Dec 12 '16 at 21:56
So then how do you get ten decimal places? And, if it is a timestamp, why wrap it within `to_date`? — , Dec 12 '16 at 21:57
If I do a straight: SELECT * FROM MyTable; That is the format that is returned (sql developer) — BlargZombie, Dec 12 '16 at 22:08
strange. In my SQL Developer I only get 9 decimals. Anyway, the solution I provided should work, just with `mydate` in the righthand side. With that said, I prefer @MT0's solution to mine. Until recently I was aware only of the DATE literal, this is the second time I see the TIMESTAMP literal in just the last couple of days. — , Dec 13 '16 at 00:33

score 1 · Answer 1 · 2016-12-12T22:05:25.527

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.

score 1 · Answer 2 · answered Dec 12 '16 at 23:39

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

answered Dec 12 '16 at 23:39

MT0

143,790
11
59
117

Using TO_DATE() with AM/PM Formatting

2 Answers2