good Oracle statement fails in SSIS with ORA-01861: literal does not match format string

The following SSIS statement works great in Oracle, and it parses successfully in the "Sql Task" window. In Oracle it returns this result:
FROM_DT              THRU_DT
16-Jan-2014      23-Jan-2014

select 
   case 
        when to_char(to_date(sysdate,'dd-mon-yyyy'),'dd') <=7 then to_date('24'||to_char(add_months(sysdate,-1),'mmyyyy'),'ddmmyyyy')
        when to_char(to_date(sysdate,'dd-mon-yyyy'),'dd') <=15 then to_date('01'||to_char(sysdate,'mmyyyy'),'ddmmyyyy')
        when to_char(to_date(sysdate,'dd-mon-yyyy'),'dd') <=23 then to_date('08'||to_char(sysdate,'mmyyyy'),'ddmmyyyy')
        else to_date('16'||to_char(sysdate,'mmyyyy'),'ddmmyyyy')
   end as from_dt,
   case 
        when to_char(to_date(sysdate,'dd-mon-yyyy'),'dd') <=7 then trunc(last_day(add_months(sysdate,-1)))
        when to_char(to_date(sysdate,'dd-mon-yyyy'),'dd') <=15 then to_date('07'||to_char(sysdate,'mmyyyy'),'ddmmyyyy')
        when to_char(to_date(sysdate,'dd-mon-yyyy'),'dd') <=23 then to_date('15'||to_char(sysdate,'mmyyyy'),'ddmmyyyy')
        else to_date('23'||to_char(sysdate,'mmyyyy'),'ddmmyyyy')
   end as thru_dt
from sys.dual

Open in new window


When I run this in SSIS, it fails with this error:
ORA-01861: literal does not match format string

The connection is Oracle, and the query parses successfully.

so what is the issue ?
LVL 1
Alaska CowboyAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
to_date(sysdate,'dd-mon-yyyy')

this is the issue. you are converting a date again to a date, so oracle is "assuming" you want to pass a string, so is converting the data implicitly to varchar first.

use sysdate simply instead
select 
   case 
        when to_char(sysdate,'dd') <=7 then to_date('24'||to_char(add_months(sysdate,-1),'mmyyyy'),'ddmmyyyy')
        when to_char(sysdate,'dd') <=15 then to_date('01'||to_char(sysdate,'mmyyyy'),'ddmmyyyy')
        when to_char(sysdate,'dd') <=23 then to_date('08'||to_char(sysdate,'mmyyyy'),'ddmmyyyy')
        else to_date('16'||to_char(sysdate,'mmyyyy'),'ddmmyyyy')
   end as from_dt,
   case 
        when to_char(sysdate,'dd') <=7 then trunc(last_day(add_months(sysdate,-1)))
        when to_char(sysdate,'dd') <=15 then to_date('07'||to_char(sysdate,'mmyyyy'),'ddmmyyyy')
        when to_char(sysdate,'dd') <=23 then to_date('15'||to_char(sysdate,'mmyyyy'),'ddmmyyyy')
        else to_date('23'||to_char(sysdate,'mmyyyy'),'ddmmyyyy')
   end as thru_dt
from sys.dual                                  

Open in new window


see this article for more explanations
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
0
 
Alaska CowboyAuthor Commented:
ahhh . . . good catch.

I was using a parameter in my Sql testing, then just switched it to "sysdate". This still worked in Oracle but then SSIS choked.

It works now !

Thanks !
0
 
Alaska CowboyAuthor Commented:
thanks for the quick response.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.