Skip to Content
Former Member
Jul 24, 2013 at 12:18 PM

Excel Workbooks file format - varchar vs datetime


Hi all,

we are facing a strange issue after migrating from Data Integrator 11.7 to Data Services 4.1 SP 1.

We have a lot of excel file format in which date fields Data Type are described as Varchar.

In Data Integrator the content of those fields were represented in the format YYYY.MM:DD HH24.MI.SS.

In Data Services the representation is set according to OS Locale settings (changing OS Locale also affects date field representation).

This affects both the transformations "where condition" (where we have explicit cast as "to_date(field, 'YYYY.MM:DD HH24.MI.SS')") and the output in the target database, where we have varchar2 fields, used in PL/SQL procedure that assumes the format is YYYY.MM:DD HH24.MI.SS.

In order to make Data Services behavior match old Data Integrator one, we have to change all date fields format (about 400 file formats) from varchar to datetime.

Does anyone faced our same problem and/or find a different solution?

We can't simple change the OS locale because in this way we would make a lot of other jobs not properly working.

Thank you