Skip to Main Content
  • Questions

  • Invalid datetime format after migrated to Oracle 12.2 client

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, swapna.

Asked: November 03, 2017 - 5:33 pm UTC

Last updated: November 04, 2017 - 1:21 am UTC

Version: oracle 12.2

Viewed 1000+ times

You Asked

Hi
We recently upgraded to Oracle 12.2. We are not able to use existing scripts to enter data which includes time in format YYYY/MM/DD HH:MM:SS ex: 2017/11/03 17:16:31 using oracle 12.2 client to insert in to Oracle 12.2 server. We are getting error "Invalid datetime format".
We tried by running query 'ALTER SYSTEM SET uniform_log_timestamp_format=false SCOPE=BOTH' as system user, but ended with same error while inserting data.
Could you please help us in resolving the issue.

Thanks & Regards
Swapna.

and Connor said...

uniform_log_timestamp_format is about the format of dates in trace files.

You might be wanting to set

nls_date_format

at session level, or perhaps system level.

Also, this can be set with an environment variable or in the windows registry.

SQL> select to_date('2017/11/03 17:16:31') from dual;
select to_date('2017/11/03 17:16:31') from dual
               *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL> alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';

Session altered.

SQL> select to_date('2017/11/03 17:16:31') from dual;

TO_DATE('2017/11/03
-------------------
2017/11/03 17:16:31


and dont forget its HH24 and MI :-)

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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