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
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'))
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
ALTER SESSION SET nls_date_format = 'MM/DD/YYYY HH24:MI:SS'; SELECT * FROM discount;