Solved

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

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

828 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