CODEWITHSUNDEEP
oracle-databasessis
Brian Paul
Brian Paul

Reputation: 651

Dates inverted when stored through SSIS to Oracle Database

I am storing dates though SSIS to an oracle table.
I am using a Execute SQL Task, when i view the dates in SSIS it looks like, example '01-APR-2008'
but when it is stored in oracle database, it stores it as '08-APR-01', as you can see the dates are inverted and i have no way controlling the behavior here.

Anyone has an idea, i can post the whole configuration if required,
The Execute SQL task has a query like the one below:

"INSERT INTO Test.TimeTable (TIME_DIM_ID)
select '"+(DT_STR, 50,1252) @[User::ReadingDate] +"'
from dual"

where @User:: ReadingDate is a variable of Data Type DateTime,
and its value is (01-APR-08 9:38:27 PM) this is just the default value, the variable is populated by another task, and the dates in the variable are then picked up by the EXECUTE SQL Task, but they are inverted at the point of storage.

Upvotes: 0

Views: 450

Answers (1)

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18818

Can you show the query that is used in the sql task?

If you know the format of the date that is coming into the database, you should always typecast the date appropriately. ( I am assuming the input is coming in as a string).

so instead of '01-APR-2008' , use to_date('01-APR-2008', 'DD-MON-YYYY') (don't rely on the session's NLS Parameters)

Upvotes: 2

Related Questions