Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Sam Sonawane.

Asked: June 05, 2017 - 3:29 pm UTC

Last updated: October 21, 2019 - 6:24 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

I have string field '2017/06/02 17:51:12.0000000' which get converted into timestamp with formatting 'YYYY/MM/DD HH24:MI:SS.FF3' correctly in 11g
I am having issue of "ora-01830: date format picture ends before converting entire input string" on 12c.

nls timestamp set to nls_timestamp_format='YYYY/MM/DD HH24:MI:SS.FF3' on 11g and 12c.

If I change ff3 to ff7 it works fine in a pkg on 12c.(to confirm)

as I do not want to change packages already using ff3.

Any suggestion would be greatly appreciated.

Thanks

and Connor said...

Unfortunately, you were relying on a *bug* in 11g that did not parse the date correctly. So 12c is the correct behaviour. But you can workaround this using "FF" without a precision, eg

SQL> select to_timestamp( '2017/06/02 17:51:12.0000000','YYYY/MM/DD HH24:MI:SS.FF3') from dual;
select to_timestamp( '2017/06/02 17:51:12.0000000','YYYY/MM/DD HH24:MI:SS.FF3') from dual
                     *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


SQL> select to_timestamp( '2017/06/02 17:51:12.0000000','YYYY/MM/DD HH24:MI:SS.FF') from dual;

TO_TIMESTAMP('2017/06/0217:51:12.0000000','YYYY/MM/DDHH24:MI:SS.FF')
---------------------------------------------------------------------------
02-JUN-17 05.51.12.000000000 PM


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

'FF3' in to_timestamp function

Sateesh Kumar Arvapalli, October 17, 2019 - 4:17 pm UTC

Thanks Tom for confirming on bug about 'FF#' usage in to_timestamp function. We had similar issue & overcame by using 'FF' in our program.
FYI - we are on "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production"- unlike youmentioned in above comment the issue is fixed in 12c, but we still see issue the with 'FF3'

'FF3' in to_timestamp function

Sateesh Kumar Arvapalli, October 17, 2019 - 4:18 pm UTC

hanks Tom for confirming on bug about 'FF#' usage in to_timestamp function. We had similar issue & overcame by using 'FF' in our program.<br>
FYI - we are on "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production"- unlike you mentioned in above comment the issue is NOT fixed in 12c, but we still see issue the with 'FF3'
Connor McDonald
October 21, 2019 - 6:24 am UTC

Not sure what you mean by "still see the issue with FF3".

FF3 should *not* work, it *should* return a ora-01830.

That is what we fixed in 12c.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library