Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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.

1 answer

0 votes
Daniel Eads Atlassian Team Aug 27, 2020

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

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
TAGS
Community showcase
Published in Confluence

Announcing Team Calendars in Confluence Data Center

Hi Community! We're thrilled to share that Team Calendars for Confluence is now a built-in feature for Confluence Data Center releases 7.11 and beyond.  A long time favorite,  Team Cale...

82 views 0 3
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you