Solved

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

Posted on 2014-01-30
3
1,822 Views
Last Modified: 2016-02-10
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 ?
0
Comment
Question by:Alaska Cowboy
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39820353
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39820361
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
 
LVL 1

Author Closing Comment

by:Alaska Cowboy
ID: 39820363
thanks for the quick response.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now