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

Answer 1

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

Answer 2

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