what is the recommended date/time format to be used with Oracle database (NLS_DATE_FORMAT)

VZ IPT Team

upon install the NLS_DATE_FORMAT is set to DD-MON-RR.

any query to the database shown on the website to troubleshoot issues is in the format 

YYYY-MM-DD HH:MM:SS

There is nothing in the install documentation for Oracle database to define what the NLS_DATE_FORMAT should be.

Daniel Eads

Hello there, welcome to the Community!

Oracle stores dates in a specific format down to the second. The NLS_DATE_FORMAT parameter controls what you can expect for default input/output of dates - there's a good explanation here on how this shakes out for Oracle databases and what you can expect when trying to modify this value .

The short explanation is that you don't need to worry about setting the value. The database driver will handle storing dates appropriately, and on the backend, Oracle will use the same storage format no matter what NLS_DATE_FORMAT is set to. If you prefer, you can change it in SQL Developer to show you different values , which can show you the details down to the second without casting the responses.

Atlassian's requirements for Oracle configuration are simply setting character encoding to AL32UTF8. Beyond that, the driver and Oracle itself take care of the rest. Since there's only one way dates are stored in Oracle, this doesn't need to be set in advance like character encoding does.

If you want to see the specific time saved with a date value and not modify the NLS_DATE_FORMAT parameter, you can also use the TO_CHAR function and tell Oracle your preferred format when retrieving DATE values, on a statement-by-statement basis:

SELECT TO_CHAR( <date_column>, 'YYYY-MM-DD HH:MM:SS' ) FROM <table>

I hope that clarifies things a bit with what to expect with date values in Oracle!

Cheers,
Daniel