DATE datatype.DATE datatype has the ability to store the month, day, year, century, hours, minutes, and seconds.But TIMESTAMP datatype which stores all the information that the DATE datatype stores,and also includes fractional seconds.
See the query results below.There is a fractional seconds part of ‘.000000’ on the end the query result.

Examples:

SQL>
SQL> set serveroutput on;
SQL>
SQL> -- Created on 11/19/2008 by Globinch SQLTIPS
SQL> declare
    -- Local variables here
    test_Date DATE;
    begin
    -- Initialize the date
    test_Date := to_date('12/12/2008 12:12:23','DD/MM/YYYY HH12:MI:SS');
    --To display date in DATE
    dbms_output.put_line('DATE : '|| test_Date);
    --To display date in TIMESATMP format
    dbms_output.put_line('TIMESTAMP: ' || CAST(test_Date AS TIMESTAMP));
  end;
/

DATE : 12-DEC-08
TIMESTAMP: 12-DEC-08 12.12.23.000000000 PM

PL/SQL procedure successfully completed

SQL>

Another example using SYSTIMESTAMP

SQL> select systimestamp from dual;
SYSTIMESTAMP
20-NOV-08 05.55.31.081000 PM +05:30

SQL>

Read,

• Oracle to_char function and usage
• Using oracle date format |Using the DATE Format Mask in pl/sql
• Oracle comparing dates : dates, oracle, date comparison in oracle pl/sql
• ORA-01882: TIMEZONE REGION NOT FOUND |Oracle Error
• PL/SQL EXTRACT function for oracle datetime

• ALTER TABLE to ADD PRIMARY KEY in Oracle| Oracle ALTER TABLE
• oracle date difference | date difference in sql an…
• DATE Data type in Oracle|Oracle Dates and Times|Dates in Oracle
• Formatting date in SQL (Oracle) -Simple date forma…

• Database Schema | Oracle Schema Objects | What is database schema?

• Database Schema | Oracle Schema Objects | What is database schema?
• Autonomous Transactions in PL/SQL
• SQL – Transaction Statements -Transaction Manageme…

Technorati Tags:
DATE vs TIMESTAMP, oracle DATE, oracle TIMESTAMP