SSMA migration from Oracle - date errors

Question

Hi,

I am trying to migrate an Oracle system (10g) to Sql Server 2008 R2. I have one table giving errors on dates on SSMA for Oracle data migration (Vn4.0.1402) -

Year, Month, and Day parameters describe an un-representable DateTime

I have also tried export, using 'Microsoft OLE DB provider for Oracle' and get errors -

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
(SQL Server Import and Export Wizard)

Answer 1

What sort of representation do you have for DateTime data in Oracle which is causing the errors during data migration?

Answer 2

Thanks for your interest.

I've just solved this by experimentation.
The date fields in question are populated via prepared statements using data from an external source.
Although the data looks valid, there does appear to be some issue when presented to SSMA.
The workround is to update the source columns in the Oracle database -
update hp2 set effective_date = to_date(to_char(effective_date,'DD-Mon-yyyy HH24:MI:SS'),'DD-Mon-yyyy HH24:MI:SS');
There is no obvious logic as to why this makes a difference, but it works.

Answer 3

I Tried with below update script but still not working for me.

I am Migrating from Oracle 11g database to Sql server 12 64 bit.

Please help i am stuck.

Year, Month, and Day parameters describe an un-representable DateTime.

update hp2 set effective_date = to_date(to_char(effective_date,'DD-Mon-yyyy HH24:MI:SS'),'DD-Mon-yyyy HH24:MI:SS');

Answer 4

I had to use this query, depending on whether the column was DATE or DATETIME:

set MyColumn = to_date(to_char(MyColumn,'DD-Mon-yyyy'),'DD-Mon-yyyy')