Date conversion warning BODS 4.2 SP3

[Former Member]

I am getting the following warning message for date fields

Cannot convert string <0> to date using format string <yyyymmdd>.

Cannot convert string < > to date using format string <yyyymmdd>.

Cannot convert string <      00> to date using format string <yyyymmdd>.

The system has been around for a while and the source data quality is poor.

There are around 81 dataflows in a job and many columns use to_date function for conversion. ( all data flows are direct mapping with only date conversion)

Some Data Flows has million records.

The source and target are Oracle / SQL Server and use ODBC connection.

1. Is there any way we can skip writing the warnings without modifying the job ?
2. When there are lots of Warnings  it is  overwriting in the Monitor log file and the Monitor in Management console is empty. Hence unable to see the progress of the job. Any solution to this ?
3. Job which usually completes in 9 mints took 38 mints to complete. Due to more warnings ?
4. Many places i have used ifthenelse(field in ('','0'),'',to_Date(field,'yyyymmdd')) and removed lots of warnings.But another day it gives error in another source. So any other way to ignore the warnings.

Regards,

Divya

[mageshwaran_subramanian · Accepted solution]

I think ifthenelse needs slight tweak

 ifthenelse(field in ('','0'),NULL,to_Date(field,'yyyymmdd'))

Also try exploring is_valid_date() function

A useful note on this scenario is available here

Error Handling - Enterprise Information Management - SCN Wiki

These types of warning definitely affect the performance.

[former_member198401 · Accepted solution]

You are correct Magesh!!

I have tweaked the to_date function as below

Divya,

You can use the below date validation to avoid the date conversion warnings

ifthenelse( is_valid_date(DATE, 'yyyymmdd') = 1,

   ifthenelse(year(to_date(DATE, 'yyyymmdd')) >= 1900,

                 to_date(DATE, 'yyyymmdd'),

                 NULL),

   NULL)

Please note that SQL Server doesnt processes dates older than 1900.

Regards

Arun Sasi

[Former Member]

Thanks Arun , Magesh for the reply.

Even I found only file format has the option to ignore conversion warnings.

Cannot change it in the source as data is coming from different application Tables.

Applying this function for all the fields is going to be a big activity since lots of dataflows are involved. 

May be that is the only option I am left with now.

Regards,

Divya

[former_member187605]

You can write a custom function pr_to_date, to make it a bit easier to change your job:

return (decode(is_valid_date($DATE, 'yyyymmdd') = 1, to_date($DATE, 'yyyymmdd'), NULL));

Then you'll have to replace all occurences of to_date by a call to this one.

[former_member198401]

Hi Divya,

The warnings will appear if the string data which you are converting to date format contains space or characters which cannot be converted by Data services.

You need to make sure that these formats are not present in the source table/files. You need to consolidate the data in Source itself such that these warnings are not displayed.

There is only one way to avoid these warnings but only when you have your Source as a flat file. This comes under the error handling part in the file format.

You can set the Log Data conversion warnings to NO and set the maximum warnings to a specific number.

Regards

Arun Sasi