[SOLVED] - ORA-01821 while parsing datetime string with Fractional Seconds (FF3 format mask for milliseconds) - ORA-01821: date format not recognized

$0
=
$0
+
$0
No specific Bitcoin Bounty has been announced by author. Still, anyone could send Bitcoin Tips to those who provide a good answer.
0

Java application is passing date and time to Oracle SPR as a string parameter that contains fractional seconds points (milliseconds). According to Oracle documentation, FF format mask is supposed to work with fractional seconds, and specifically FF3 should work for parsing those milliseconds. However I am getting the following Oracle error ORA-01821: date format not recognized :

-- this code does not work - it throws the following ORACLE error:
-- ORA-01821: date format not recognized
--
SELECT TO_DATE('2010-12-25 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF3') 
FROM DUAL;

-- removed FF3 from the format mask - still does not work -
-- it throws ORA-01830: date format picture ends before converting entire input string
SELECT TO_DATE('2010-12-25 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS') 
FROM DUAL;

-- this works, b/c fractinal seconds mask has been removed from the formatting string, AND milliseconds have been removed from the INPUT string:
SELECT TO_DATE('2010-12-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
FROM DUAL;

The latest statement works, but I had to parse the input string and manually remove the milliseconds part. I would rather not do that and just use the FF or whatever is supposed to work with fractional seconds.

Does anyone know how to parse those milliseconds and why FF mask is not working as expected? Thanks!

Tags:

1 Answer - total earned - 0 BTC ($0 USD)

1
=
0
=
$0
Everyone, even non-registered users could send Bitcoin Tips to YOU if they are inspired by your answer!

So I've found a solution - you parse milliseconds into TIMESTAMP first and then CAST it into DATE:

select  cast(to_timestamp('2010-12-25 00:00:00.000','yyyy-mm-dd HH24:MI:SS.FF3') as date) from dual;

For some reason, FF mask only works with TIMESTAMP datatype and it does not work with DATE datatype. Here is a bit of a mix of what works and what does not work:

SELECT TO_DATE      ('2010-12-25 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL; -- ORA-01821: date format not recognized
SELECT TO_TIMESTAMP ('2010-12-25 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL; -- WORKS FINE

SELECT TO_CHAR (SYSDATE      ,'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL; -- ORA-01821: date format not recognized
SELECT TO_CHAR (SYSTIMESTAMP ,'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL; -- WORKS FINE
SEND BITCOIN TIPS
0

Post Answer


Will Bitcoin Tipping become a new Internet Culture?


Please share our story! THANK YOU!