1 Reply Latest reply: Apr 7, 2013 11:23 AM by Paul M. RSS

    Date function problem

    1001528
      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.
          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).