search cancel

Check and set NLS_DATE_FORMAT

book

Article ID: 49187

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

How to make sure NLS_DATE_FORMAT is correct and set to "YYYY-MM-DD HH24:MI:SS"

Resolution

  1. Run the following SQL queries against your database:

    select 1 as id, parameter, value from nls_database_parameters
    where parameter = 'NLS_DATE_FORMAT'
    union
    select 2 as id, parameter, value from nls_instance_parameters
    where parameter = 'NLS_DATE_FORMAT'

  2. Log as sysdba and then run this query:

    alter system set nls_date_format="yyyy-mm-dd hh24:mi:ss" scope=spfile;

  3. Restart Oracle Services.

Additional Information

Also check the issue caused by setting this wrong:  Clarity with Oracle throws ORA-01861: literal does not match format string