The new TIMESTAMP datatype

Oracle has expanded on the DATE datatype and has given us the TIMESTAMP datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds.


If you want to convert a DATE datatype to a TIMESTAMP datatype format, just use the CAST function. As you can see, there is a fractional seconds part of '.000000' on the end of this conversion. This is only because when converting from the DATE datatype that does not have the fractional seconds it defaults to zeros and the display is defaulted to the default timestamp format.

CREATE TABLE date_table (
date1 DATE,
time1 TIMESTAMP,
time2 TIMESTAMP
);

INSERT INTO date_table (date1, time1, time2)
VALUES (SYSDATE,
TO_TIMESTAMP ('17.12.1980:00:00:00','DD.MM.YYYY:HH24:MI:SS'),
TO_TIMESTAMP ('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
);
COMMIT;

SELECT CAST(date1 AS TIMESTAMP) "Date" FROM date_table;

Date
---------------------------------------------------------------------------
03-DEC-04 11.36.45.000000 AM