NLS_TIME_FORMAT not the same as what data is being entered.

[Ratzie]

I have some underlying questions regarding time stamps. I thought I would post new

Our application uses YYYY is all date formats.
I have finally found out how to show the oracle date parameters.

Using command sqlplus,
SQL> select sysdate from dual;
SYSDATE
---------
23-MAR-06
Makes sense because of the NLS parameters.
BUT, in toad it shows as:

SELECT SYSDATE FROM DUAL
SYSDATE
-----------------------
03/23/2006 10:09:37 AM
1 row selected

Toad must have its own formating.
But, here is what is confusing me.

Our application only accepts YYYY MM DD HH24:MI
So does oracle know to convert this data to the NLS TIME format?

What I need to know is how to properly enter data that contains a date format into the database.


SQL> select * from v$nls_parameters;
NLS_TIME_FORMAT
HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM

[Peter Godron]

Hi again,
you can always insert date data as:
insert into table values (to_date('DD-MON-YYYYHH24:MI:SS','31-DEC-200123:58:30'));
The data is held in the database in its special format and can then be extracted into any form:
select to_char(sysdate,'DD-YY HH:MI') from table;
31-01 23:58

[Peter Godron · Accepted solution]

Hi,
soory, but my fingers are not ins tep with my brain.
Insert command should be:
insert into table values (to_date('31-DEC-200123:58:30','DD-MON-YYYYHH24:MI:SS'));

[Hein van den Heuvel]

The date are stored in the datase as 'binary'/raw value in DATE columns.
There is no formatting at all.
For the DB itself all data stored in a date column is equal (except the actual values of course :-) not matter which format was used to get it in there, or to disply on the output.

You 'toad' observation proves this.
The same value, can and will be displayed differently based on the client code (SQLplus is a client, like Toad is and like your application is).

This is much the same as how integers are treated. For example, a value 10,000 whether entered as 10**4, or 100000 or hex:2710 will be stored as bit pattern "0010011100010000".
And it can then be displayed as 10000 or 10,000 or 00001000 or **** based on the client sofware code.

fwiw,
Hein.

[Indira Aramandla]

Hi LHradowy,

Oracle Server stores dates in the internal binary date format, which is independent of cultural conventions used in countries around the world. Dates are stored in the Gregorian calendar and consist of binary coded year (4 digits), month, day, hour, minutes and seconds.The format model is determined by the current value of the NLS_DATE_FORMAT setting.

DATE datatypes in ORACLE can store both the date and time. You can use DATE to keep just a date, just a time, or both, but internally ORACLE will store the information as date & time together. DATE is commonly used to store only the date, however ORACLE will use the default time of 12 Midnight when storing a DATE column that does not have the time specified. The date format when inserting into the table will be as you specidied in the NLS_DATE_FORMAT parameter.

You can use the TO_CHAR and TO_DATE functions to store dates with time, or just time into a DATE column in ORACLE. When you want to retrieve the information back out of the database, use the TO_CHAR function in your SELECT statement to reverse the process and bring the time component into a String.


Indira A

[Yogeeraj_1]

hi,

Note that a date is stored in an internal 7 byte format.


The formats are EDITS applied to the bits and bytes that convert them from the internal format (a DATE) to a string so we can read them.

The dates are not STORED using that format -- the format is used only for DISPLAY when you select it out. All dates are stored in a 7 byte machine format (and in 9i, a timestamp might take 11 bytes for timezones and fractional seconds).

see also the docs at:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c10datyp.htm#796

hope this helps too!

kind regards
yogeeraj