Solved

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

Posted on 2014-01-30
3
1,786 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

707 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