how to insert date and time in oracle?

Just use TO_DATE() function to convert string to DATE.

For Example:

create table Customer(
       CustId int primary key,
       CustName varchar(20),
       DOB date);

insert into Customer values(1,'Vishnu', TO_DATE('1994/12/16 12:00:00', 'yyyy/mm/dd hh:mi:ss'));

Try this:

...(to_date('2011/04/22 08:30:00', 'yyyy/mm/dd hh24:mi:ss'));


You can use

insert into table_name
(date_field)
values
(TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'));

Hope it helps.


You are doing everything right by using a to_date function and specifying the time. The time is there in the database. The trouble is just that when you select a column of DATE datatype from the database, the default format mask doesn't show the time. If you issue a

alter session set nls_date_format = 'dd/MON/yyyy hh24:mi:ss'

or something similar including a time component, you will see that the time successfully made it into the database.

Tags:

Oracle