Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

oracle dates

SOLVED
Go to solution
Ratzie
Super Advisor

oracle dates

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.
4 REPLIES
Peter Godron
Honored Contributor
Solution

Re: oracle dates

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
Honored Contributor

Re: oracle dates

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
Super Advisor

Re: oracle dates

Appreciate all your thoughts!
Eric Antunes
Honored Contributor

Re: oracle dates

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
Each and every day is a good day to learn.