Member Avatar for pointers

Hi,

I have a question. Please help me out.

What is the default "display date format" and default internal "accepting date format"? How to change the default display and accepting format. In my system, the default display date format in sql * plus is dd-mon-yy, where as in toad it is mm/dd/yyy HH:MI:SS AM

Can we change these two default display date format.I know I can use to_char. But without any explicit conversion functions during the selection.

insert into mine_date values(to_date('22/03/1984','dd/mm/yyyy'))

What is the internal date format of the date(22/03/1984)that has been changed by to_date function.

  • 3 Contributors
  • 2 Replies
  • 89 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

Short answer: Oracle does not store dates as formatted text. As noted, the client controls display. Internally an Oracle DATE is a compact binary structure (the stored fields are year/century, month, day, hour, minute, second), so there is no permanent "text format" attached to a DATE value. TO_DATE converts a string into that internal DATE; formatting only matters when converting back to text.

To inspect and set what your session shows (this is why SQL*Plus and TOAD look different), check the session NLS setting and change it for the session if needed. For example, to see the session format:

SELECT value FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';

To change the display for the current session:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

As suggested, the database-level NLS defaults live in the NLS_ views, but client tools (TOAD, SQLPlus) often apply their own defaults or run an "on connect" script. For persistent client behavior put an ALTER SESSION in your SQL*Plus login.sql/glogin.sql or use TOAD's "execute on connect" option.

Practical cautions: always use explicit conversions (explicit format masks with TO_DATE/TO_CHAR) in scripts to avoid ambiguity between clients and locales. Remember Oracle DATE has seconds precision; use TIMESTAMP types when you need fractional seconds or time zone handling.

Recommended Answers

All 2 Replies

Member Avatar for jwenting

Oracle stores dates as numbers, the display format is ALWAYS determined by the client, not the server.

Member Avatar for debasisdas

Check the data foramt by using this

select * from nls_database_parameters

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.