Date value conversion in SAP BODS

[Former Member]

SAP table PRPS has a date field 'USR08' and when trying to extract using BODS, blank date values in SAP are getting converted into some default values.

When the target field is set as varchar  ,

(i.e). SAP_PRPS.USR08(date) ---> BODS.USR08(VARCHAR)

Explicit usage of to_Char (Blank --> 19000101)

Direct casting to varchar (Blank --> 00000000)

both are giving different defaults in place of blank.

When the target field is also set as Date ,

(i.e). Direct extract of date

PRPS.USR08(date) ---> BODS.USR08(date)

(Blank --> 1900.01.01)

Format of date (YYYYMMDD,DDMMYYY,YYYY.DD.MM,YYYY-DD-MM) is not the issue. Default in places of blank is the only issue.

What could be the reason for blanks getting converted into random defaults ?

Note : Version of BODS used : 4.2 , DB Connection : SQL Server management Studio 2008

[mageshwaran_subramanian · Accepted solution]

Can you check if Tool -> Options -> SAP -> Convert SAP null to null is set?. If not , choose this option to get rid of default conversion.

[Former Member · Accepted solution]

Yes , This is working . But the problem with having this option set is that it affects other SAP table 'Blank' values and in-turn transformations built on them. Is there any option to set the same at table or field Or Job level ?

[mageshwaran_subramanian · Accepted solution]

There's no setting to do this at the job or table level I'm afraid

The work around would be,

Let DS convert the SAP NULL into 1900.01.01 , then you convert 1900.01.01 back to NULL or blank

Decode ( date_field = '1900.01.01' , NULL , date_field)

I hope PRPS table doesn't contain any records with date as '1900.01.01'

[Former Member · Accepted solution]

This workaround was tried already and it didn't seem to work. Unfortunately , we have certain records with date '1900.01.01' , and when we tried this we were not able to differentiate '1900.01.01' actual values and defaulted values.

[mageshwaran_subramanian · Accepted solution]

I guess date 1900.01.01 in SAP is meant to be blank .I would suggest you to speak to the SAP functional consultant or the data owner to see if they are valid dates.

[Former Member · Accepted solution]

Thanks for the suggestion ! This has already been confirmed as Invalid date by data owner. One confusing point is that when Tool -> Options -> SAP -> Convert SAP null to null was set , they were extracted as just NULLS and methods other than BODS to extract same data from SAP gave NULLs as NULLs only. So I infer that it is not a setting of SAP that is causing this issue , but something at BODS/SQL level.Trying to make other workarounds to fix this, hopefully!