Date format in Dynamic data type

[tnewbie]

In the development environment, I have created one job which can read from 120+ data dump files and load the corresponding tables. For my development purpose I assumed the date format as 'dd-mon-yyyy' when I created the sample files.

When I started getting the production data of these dumps there was a change in the date formats and the generic job is failing to load the data into the tables, the issue is with the date format. Date format in the incoming dump file is "yyyy-MM-dd.hh24.mi.ss" and the generic/dynamic data type is not able to detect it as a date value and is throwing out the much known oracle error ORA-01843

Any ideas on how to format date values when using a Dynamic Data type....please share

[manodwhb]

@tech seeker​ , if you wan to make dynamic you should request to get the unified format date column in all the files.

other wise you need to go with static schema and need to design each job for each file to process.

Thanks,

Manohar

[tnewbie]

Thanks for the reply Manohar.

When you say unified format, are you referring to have the same format in all the files? If so, the answer is Yes all the files do have the format "yyyy-MM-dd.hh24.mi.ss"  for all the incoming dates, my question is, when you define the dynamic data type, I tried to give the same format as the pattern of my date columns in the dynamic datatype, but it doesn't seem to be helping. I am not knowing where should the date be formated, because when i had 'dd-mon-yyyy' as my date format in my sample data files load went through, though I had 'dd-MM-yyyy' as the date format in my dynamic data type, but when i started getting "yyyy-MM-dd.hh24.mi.ss"  as my date format in my data dumps loads are failing.

Any suggestions in context...Please share

[tnewbie]

Hello All, any thoughts on this...

[DSuryadevara]

Hello , i Am having same requirement need to convert to date type when data type is set to dynamic, did you get answer for this?