1

I have timestamp (05-DEC-15 03.48.57.061000000 PM) in one of our tables. I want to compare this timestamp to retrieve data. I have written following query

select * from ACTIVATIONRECORDS
WHERE ACTIVATIONDATE =
            TO_DATE('04-12-15 03.48.57.052000000 PM','DD-MM-YY HH.MI.SS.SSSSS PM');

But its failing with following error

ORA-01861: literal does not match format string
01861. 00000 -  "literal does not match format string"
*Cause:    Literals in the input must be the same length as literals in
           the format string (with the exception of leading whitespace).  If the
           "FX" modifier has been toggled on, the literal must match exactly,
           with no extra whitespace.
*Action:   Correct the format string to match the literal.
jarlh
  • 42,561
  • 8
  • 45
  • 63
Preyas
  • 19
  • 3
  • TO_DATE('04-12-15 03.48.57.052000000 PM','DD-MM-YY HH.MI.SS.SSSSSSSSS PM');? – jarlh Nov 30 '15 at 11:47
  • 1
    Possible duplicate of [String to date in Oracle with milliseconds](http://stackoverflow.com/questions/1758219/string-to-date-in-oracle-with-milliseconds) – peter.hrasko.sk Nov 30 '15 at 12:10

2 Answers2

3

Data type DATE does not provide any fractional seconds, thus the format does not work. Use TIMESTAMP instead.

Fractional seconds are represented by FF instead of SSSSS.

Then you should use either four digits for the year or use RR instead of YY.

And you should use x for "Local radix character" instead of hard-coded dot.

This one works:

TO_TIMESTAMP('04-12-15 03.48.57.052000000 PM','DD-MM-RR HH.MI.SSxFF PM')
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
1

Please note that "SSSSS" for Seconds past midnight (0-86399).

 CAST(TO_TIMESTAMP('05-DEC-15 03.48.57.061000000 PM', 'YYYY-MM-DD HH:MI:SS.FF9 AM') AS DATE)

http://www.techonthenet.com/oracle/functions/to_date.php

Nisha Salim
  • 687
  • 5
  • 13