Solved

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

Posted on 2014-01-30
3
1,898 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

738 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