This discussion is archived
1 Reply Latest reply: Apr 7, 2013 9:23 AM by Paul M. RSS

Date function problem

1001528 Newbie
Currently Being Moderated
i am student and using Oracle 11g XE, now while trying out the date function..when time 15:00:00 was entered an error is being shown
the * is being shown under 15:00:00

SQL> create table ticket_header(ticket_no number(5), doi date, dot date, time_travel date, board_place varchar2(15), origin varchar2(15), destination varchar2(15), adults number(5), children number(5), total_fare number(5), route number(5));

SQL> insert into ticket_header values(01, '01-apr-96', '10-may-96', '15:00:00', 'Parrys', 'Madras', 'Madhurai', 1, 1, 60, 101);
insert into ticket_header values(01, '01-apr-96', '10-may-96', '15:00:00', 'Parrys', 'Madras', 'Madhurai', 1, 1, 60, 101)
*
ERROR at line 1:
ORA-01843: not a valid month
  • 1. Re: Date function problem
    Paul M. Oracle ACE
    Currently Being Moderated
    What's the purpose of time_travel column ? Will you do calculations on it, or it's only for reporting ? If the former, then date is better, otherwise you could use a varchar2 column. See this example :
    SQL> create table test(a date, b varchar2(8));
    
    Table created.
    
    SQL> insert into test values(to_date('15:00:00','hh24:mi:ss'),'15:00:00');
    
    1 row created.
    
    SQL> select to_char(a,'hh24:mi:ss'),b from test;
    
    TO_CHAR(A,'HH24:MI:SS')  B
    ------------------------ ------------------------
    15:00:00                 15:00:00
    
    SQL>
    About the error, you get it because your date format is 'DD-MON-RR' (the default), and inserted values have to conform with it, as in your other date columns, or you have to format them (e.g. to_date).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points