oracle dates

[Ratzie]

How do I find out how a date column expects the date format.

We have data that needs to be changed to be identical like what we have on a certain database, and the data type just shows DATE.

[Peter Godron · Accepted solution]

Hi,
the date format is stored in a special internal Oracle format that includes not just month,day and year, but also hour,minute and second.
So you can do a :
select to_char(sysdate,'DD-MON-YYYYHH24:MI:SS') from dual;
and get the current system time, down to seconds.
Try the same on your table and see the level of accuracy required, then insert your date with a to_date function.

[Hein van den Heuvel]

Under what circumstances do you see differences, and how wouls you describe those difference.
- working in the same instance?
- working in the same account (glogin.sql settings?)
- Some 'Column xxx Format An' active? (re-try CLEAR COL)


The SQLplus COLUMN description has some description that may help you:
http://download-west.oracle.com/docs/cd/B12037_01/server.101/b12170/ch13.htm

"The default width and format of unformatted DATE columns in SQL*Plus is derived from the NLS_DATE_FORMAT parameter. The NLS_DATE_FORMAT setting is determined by the NLS territory parameter. For example, the default format for the NLS territory, America, is DD-Mon-RR, and the default width is A9. The NLS parameters may be set in your database parameter file, in environment variables or an equivalent platform-specific mechanism. They may also be specified for each session with the ALTER SESSION command. For more information about DATE formats, and about NLS parameters, see the Oracle Database SQL Reference.

You can change the format of any DATE column using the SQL function TO_CHAR in your SQL SELECT statement. You may also wish to use an explicit COLUMN FORMAT command to adjust the column width.

:
To change the width of a DATE column to n, use the COLUMN command with FORMAT An. If you specify a width shorter than the column heading, the heading is truncated."

Google: +oracle +date +format
This of course gives tons of hints.
Link to one of many examples below.

Hope this helps some,
Hein.


http://www-db.stanford.edu/~ullman/fcdb/oracle/or-time.html

[Ratzie]

Appreciate all your thoughts!

[Eric Antunes]

Hi,

The DATE is the data type of the table column and is equal for EVERY database.

The date FORMAT is what you want to show to users.

Example 1: if you don't want to show time to the users, just date, you format the field with 'DD-MON-RRRR'.

Example 2: if you just don't want to show the seconds, just date hours and minutes, to users you format the field with 'DD-MON-RRRR HH24:MI'.

...

Best Regards,

Eric Antunes