none
ORA-01843: not a valid month

    Question

  • I am creating a table in Oracle using the execute sql task of ssis..the code is as follows:

    BEGIN
    
    execute immediate '
    create table mem1 nologging as
    select distinct t1.*,
    CAST(TO_CHAR(ADD_MONTHS(TO_DATE(sysdate, ''MM/DD/YYYY''), -3), ''yyyymm'') AS NUMBER) as Report_mth
    from pat_1mon t1,
         pat_6mon t2
         where t1.pat_uid = t2.pat_uid (+)
                and t2.pat_uid is null
    group by t1.pat_uid';
    
    END;

    I am getting the following error when the package is executed:

    [Execute SQL Task] Error: Executing the query "BEGIN

    execute immediate '
    create table mem1 nolog..." failed with the following error: "ORA-01843: not a valid month
    ORA-06512: at line 1". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    If I run the same query in Oracle without 'execute immediate' part, then I am getting the correct result.

    Can someone please help me out here..

    Friday, May 11, 2012 9:47 PM

Answers

  • I can guess that following is the problem:

    TO_DATE(sysdate, ''MM/DD/YYYY'')

    1. You are converting date/time to string (using localized format) and then back to date using defined format. It will fail if default format is not 'MM/DD/YYYY'.

    2. If you just need to trim time out of sysdate, use TRUNC(sysdate, 'DD') instead:

    CAST(TO_CHAR(ADD_MONTHS(TRUNC(sysdate, ''DD''), -3), ''yyyymm'') AS NUMBER) as Report_mth

    • Proposed as answer by Eileen Zhao Monday, May 14, 2012 8:52 AM
    • Marked as answer by rockstar283 Monday, May 14, 2012 5:03 PM
    Saturday, May 12, 2012 3:49 AM

All replies

  • I can guess that following is the problem:

    TO_DATE(sysdate, ''MM/DD/YYYY'')

    1. You are converting date/time to string (using localized format) and then back to date using defined format. It will fail if default format is not 'MM/DD/YYYY'.

    2. If you just need to trim time out of sysdate, use TRUNC(sysdate, 'DD') instead:

    CAST(TO_CHAR(ADD_MONTHS(TRUNC(sysdate, ''DD''), -3), ''yyyymm'') AS NUMBER) as Report_mth

    • Proposed as answer by Eileen Zhao Monday, May 14, 2012 8:52 AM
    • Marked as answer by rockstar283 Monday, May 14, 2012 5:03 PM
    Saturday, May 12, 2012 3:49 AM
  • Thanks a lot mate..that worked like a charm..

    but still wondering..my code was working in Oracle but when same code executed from SSIS..it did not work

    Monday, May 14, 2012 5:02 PM