1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-01843: not a valid month

Discussion in 'SQL PL/SQL' started by Vicky, Jun 23, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    While executing the query below,

    insert into emp(hiredate) values (to_char(TO_DATE(sysdate,'dd/mm/rrrr***hh24:mi' ), 'dd/mm/rrrr***hh24:mi'));

    I'm facing this problem.,.

    insert into emp(hiredate) values (to_char(TO_DATE(sysdate,'dd/mm/rrrr***hh24:mi' ), 'dd/mm/rrrr***hh24:mi'))
    Error report:
    SQL Error: ORA-01843: not a valid month
    01843. 00000 - "not a valid month"
    *Cause:
    *Action:

    desc emp

    Name Null Type
    -------- ---- ------------
    EMP_ID NUMBER(4)
    ENAME VARCHAR2(50)
    HIREDATE DATE
    COMM NUMBER(7,2)
    DEPTNO NUMBER(2)

    COuld you tell me wat's the problm with the query?!
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Vky,

    HIREDATE is of type DATE. You can directly insert SYSDATE into that field. But Why you are converting SYSDATE to char ???


    Regards
    Sambasiva Reddy.K
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    You don't need to re-convert a date to date: SYSDATE is already DATE type.
    So, you just have to do:

    Code (SQL):
    INSERT INTO emp(hiredate) VALUES (SYSDATE);
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Date data type will accept dd/mon/rrrr but not dd/mm/rrrr also it will not accept dd/mon/rrrr hh24:mi:ss .

    You can insert like this....

    insert into emp(hiredate) values (to_char(TO_DATE(sysdate,'dd/mon/rrrr' ), 'dd/mon/rrrr'));

    As samba stated that no need to convert sysdate, directly insert sysdate...
     
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Actually, I was not trying to insert sysdate, I showed tat as a example.,.

    Here's the actual query,

    insert into emp(hiredate) values (to_char(TO_DATE('05/06/2012 03:00','dd/mm/rrrr***hh24:mi' ), 'dd/mm/rrrr***hh24:mi'));


    I want to insert date along with time,. I already tried altering session, but eventhough it's inserting date alone.,.

    but select statement work's.,.

    select to_char(to_date('06/05/2012 03:25', 'dd/mm/yyyy hh24:mi'), 'dd/mon/yyyy hh24:mi') from dual;

    while this works, why not insert.,?!

    Actually, I'm executing this query inside a package.,.
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    If hiredate column is timestamp data type then it is possible to insert date along with time.
     
  7. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi ,

    Try with below statement.

    Code (SQL):
    INSERT INTO emp(hiredate) VALUES (to_date('06/05/2012 03:25', 'dd/mm/yyyy hh24:mi'));
    Regards
    Sambasiva Reddy.K
     
  8. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Vicky,

    select statement will pull any type of data and we converted the date format to character format using to_char function. But the table column (HIREDATE) is designed based on Date Data Type. So we need to insert only date format to that table.

    If we still want to insert characters to that column, define the column format as Varchar2 and try to insert the data to that table using same insert statement and let us know.
     
  9. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi all,

    Thanks for every one., The query gvn by Samba, works fine.,. But it never worked when I tried before, using to_date function alone., I might made some mistake., And Thanks for ur support.,.