CODEWITHSUNDEEP
oracle10g
Gaurav
Gaurav

Reputation: 103

how to insert a time in oracle 10g database

I want to insert date and time in oracle database, I have created the table with columns

create table myadmin 
(  employe_id number(5), 
  supervisor Varchar2(20),
  department Varchar2(20),
  action Varchar2(20),
  sdate date, 
  stime date) 
While inserting the values below it gives an error. Please tell me how to insert the time ?

insert into myadmin 
(  employe_id,supervisor,department,action,sdate,stime) values 
(83,'gaurav','helpdesk','pick','23-jan-2013','09:43:00');

Upvotes: 0

Views: 1280

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95031

A date in Oracle always has a date part and a time part. Having date and time in two separate columns only makes sense, if it can occur that date is null and time is not. (And still, you could set date to an improbable value like 1.1.0001 then.)

However, if you want to stick to those two separate fields, then make your string a datetime with the to_date function specifying the format used:

insert into myadmin 
(  employe_id,supervisor,department,action,sdate,stime) values 
(83,'gaurav','helpdesk','pick',to_date('23-01-2013','dd-mm-yyyy'), to_date('09:43:00', 'hh24:mi:ss'));

Upvotes: 1

Ram Sharma
Ram Sharma

Reputation: 8819

You have to use keyword to_date for date insert in oracle like this.

to_date('23-01-2013','dd-mm-yyyy')

Basically you have to use keyword to_date('your date','your date format').

You can also add date and time together if you want and it would be something like this

 to_date('23-01-2013 09:43:00','dd-mm-yyyy hh24:mi:ss')

Upvotes: 1

Related Questions