|
|
| Re: how can i write a table script having a column containing time [message #9288 is a reply to message #9287] |
Thu, 30 October 2003 23:43   |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Oracle will always store date information with a time fraction. If you ommit the time fraction, it will default to 00h00'00''. If you ommit the date fraction and provide only the time fraction, it will default to the first of the month at the time provided. So creation of a table with a date_column containing time info is as simple as this:
CREATE TABLE <I>table_name</I>( numeric_column NUMBER
, <B>date_column <FONT COLOR=RED>DATE</FONT></B>
)
Although you won't notice, Oracle will always store Date values up to seconds. But, by default the display format is (in most cases): DD-MON-YY(YY). The default display format is the NLS Date Format. You can change it by using an SQL> ALTER SESSION SET NLS_DATE_FORMAT='some other date format' SQL> Select sysdate System_date
2 From dual;
SYSTEM_DA
---------
31-OCT-03
SQL> Select to_char(Sysdate, 'MONTH DDSPTH YEAR HH24:MI:SS') some_date
2 From dual
3 /
SOME_DATE
----------------------------------------------------------------------------
OCTOBER THIRTY-FIRST TWO THOUSAND THREE 07:46:14
SQL>
As of Oracle9i, there are new datatypes especially designed for timestamps, which can contain fractions of seconds.
But for more information I suggest that you visit http://tahiti.oracle.com (the Oracle online documentation, requires a free subscription).
Here's a part of the doc:
DATE Datatype
<!--/TOC=h2-->
The DATE datatype stores date and time information. Although date and time information can be represented in both
CHAR and NUMBER datatypes, the DATE datatype has special associated properties. For each
DATE value, Oracle stores the following information: century, year, month, day, hour, minute, and second.
If you specify a date value without a time component, the default time is 12:00:00 AM (midnight). If you specify a time value without a date,
the default date is the first day of the current month. The date function SYSDATE returns the current date and time.
The default date format is specified by the initialization parameter NLS_DATE_FORMAT and is a string such as
'DD-MON-YY'. This example default date format includes a two-digit number for the day of the month, an abbreviation of the month
name, and the last two digits of the year. Oracle automatically converts character values that are in the default date format into
DATE values when they are used in date expressions.
To specify a date value that is not in the default format, you must convert a character or numeric value to a date value with the
TO_DATE function. In this case, you must specify the nondefault date format model (sometimes called a "date mask") to tell
Oracle how to interpret the character or numeric value. For example, the date format model for '17:45:29' is 'HH24:MI:SS'. The date format
model for '11-NOV-1999' is 'DD-MON-YYYY'.
MHE
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
