Which data type is used to store DateTime in Oracle 10g?

Question

Which data type is used to store date time (not only date but also time) in Oracle 10g database? I have read some where that (may be mistakenly) "the Date data type in Oracle 10g can store date time" but the thing doesn't seem to happen when I try to do so.

The DATE datatype is a datetime datatype. It stores a date and a time. The date portion is based on the number of days since January 1, 4712 BC. The time portion is based on the number of seconds since mid-night. The link

I tried it through Java as a front-end and directly on the Oracle terminal using the following SQL.

insert into discount 
values(7, 'Code', 12.5, to_date('11-AUG-2012 06:05:23', 'dd-mon-yyyy hh:mi:ss'), 
to_date('20-AUG-2012 07:50:23', 'dd-mon-yyyy hh:mi:ss'))

It works but (in both the cases) Oracle simply ignores the time portion and inserts only the dates specified in the SQL without any error?

Which data type is used to store date time in Oracle 10g? Is it TimeStamp?

Answer 1

A DATE in Oracle always has a day component and a time component. Assuming the last two columns in the DISCOUNT table are declared as DATE, it is terribly unlikely that Oracle is simply ignoring the time component.

What makes you believe that the time component is being ignored? My guess is that you are checking the data by running a query like

SELECT *
  FROM discount

from SQL*Plus, SQL Developer, TOAD, or some other GUI. If you do that, by default, the date that is displayed will only have a day component and not a time component That does not mean that the time component is being discarded. Instead, it is the result of having a default NLS_DATE_FORMAT that doesn't display the time component. You can force the time component to be displayed by doing an explicit TO_CHAR in your query, (note that I'm guessing at the names of your columns)

SELECT discount_id, 
       discount_type, 
       discount_amt, 
       to_char( discount_start_date, 'MM/DD/YYYY HH24:MI:SS' ),
       to_char( discount_end_date, 'MM/DD/YYYY HH24:MI:SS' )
  FROM discount

Or by changing your default NLS_DATE_FORMAT

ALTER SESSION SET nls_date_format = 'MM/DD/YYYY HH24:MI:SS';
SELECT *
  FROM discount;

Answer 2

Oracle DATE does include the time as well. From the docs:

Overview of DATE Datatype

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).

It may be the tool(SQL Developer, SQL*Plus) you use that is cheating you. Try

select to_char(your_date, 'dd-mm-yy hh:mi:ss') from discount

Also note that the date datatype stores only till the seconds. TIMESTAMP or TIMESTAMP WITH TIME ZONE are more precise.