Re: DATE datatype(Is the Time in there too?)

cut/paste from Oracle Product Documentation Library R1.0.16 gives:

"The DATE datatype stores point-in-time values, such as dates and times.
Date data is stored in fixed length fields of seven bytes each. "

and...

"The DATE datatype stores point-in-time values (dates and times) in a table.
The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight). Oracle can store dates ranging from Jan 1, 4712 BC through Dec 31, 4712 AD. Unless you specifically specify BC, AD date entries are the default.

Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.

For input and output of dates, the standard Oracle default date format is DD-MON-YY, as below:

'13-NOV-92' You can change this default date format for an instance with the parameter NLS_DATE_FORMAT. You can also change it during a user session with the ALTER SESSION statement. To enter dates that are not in standard Oracle date format, use the TO_DATE function with a format mask:

TO_DATE ('November 13, 1992', 'MONTH DD, YYYY')

Note: If you use the standard date format DD-MON-YY, YY indicates the year in the 20th century (for example, 31-DEC-92 is December 31, 1992). If you want to indicate years in any century other than the 20th century, use a different format mask, as shown above.

Oracle stores time in 24-hour format -- HH:MI:SS. By default, the time in a date field is 12:00:00 A.M. (midnight) if no time portion is entered. In a time-only entry, the date portion defaults to the first day of the current month. To enter the time portion of a date, the TO_DATE function must be used with a format mask indicating the time portion, as in

INSERT INTO birthdays (bname, bday) VALUES ('ANDY',TO_DATE('13-AUG-66 12:56 A.M.','DD-MON-YY HH:MI A.M.'));
"

...neil and the Oracle Ducumentation CD {actually neil [dot] hulin [at] litech [dot] freeserve [dot] co [dot] uk} Received on Thu Nov 12 1998 - 04:01:06 CST