Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Unanswered: Oracle invalid month..

    create table event(etime timestamp,ename varchar(20),edate timestamp,edescription varchar(250))

    i m trying to insert a value like this


    insert into event values(to_char(sysdate,'HH12:MI:ss am'),'Birthday',to_char(sysdate,'dd-MON-yyyy'),'Party')


    i m getting ORA-01843: not a valid month tell me the solution

    i need time in one column and date in other column

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Your insert is equivalent to something like:

    Code:
    insert into event values('11:23:45 am','Birthday','24-dec-2008','Party');
    You have 2 strings in different formats ('11:23:45 am' and '24-dec-2008') that you are inserting into a column of type TIMESTAMP. Oracle therefore needs to convert the strings to timestamps for you via an implicit conversion, which it does with the default format mask for your session, which is typically 'DD-MON-RR'. '11:23:45 am' doesn't match the format mask, hence the error.

    I would not recommend splitting the date and time into 2 separate columns, but if I did I would probably do this:
    Code:
    create table event(etime varchar2(11), ename varchar(20),
      edate date, edescription varchar(250));
    
    insert into event values (to_char(sysdate,'HH12:MI:ss am'), 'Birthday',
        trunc(sysdate), 'Party');

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •