Hi experts I am facing issue while extracting date columns from SAP application

[Former Member]

Hi While extracting date fields I am facing some issue. In SAP end date format is DD.MM.YYYY and after importing the tables in BODS end the data preview for date columns are like YYYY.MM.DD. I have used various commands like to_char(exp,'YYYY/MM/DD),To_char(YYYYMMDD) , To_date(),CAST() but all the time it is giving warnings and limit is exceeding and ultimately the job is failing. Please help me resolving the issue

[jayant_mittal2]

Your source is ECC i guess.. and if  you are loading your table without any conversions in the query transform then it should not show any warnings.

try to load the target without doing any conversions in the query transform and check in your target table, don't worry about data preview in BODS designer.

[Former Member]

Ya correct It is ECC.Ya I have tried without converting only. Actually I don't want any conversation.... But don't know why I never faced this type of issue...same warnings every time. checked in source dump as well format is DD.MM.YYYY.

[jayant_mittal2]

Hi,

Are you trying to filter Data using End date values or just loading entire Data to target database??

if you are going to use ENDDA in where condition then the syntax should be like : PA0041.ENDDA =to_date('99991231' ,'YYYYMMDD');

If your back end database is Oracle, the Date values will be saved as 'DD-MON-YY' in your target table.

[Former Member]

My Source and target both are in SQL Server and i/p date format is YYYY.MM.DD . I have used all those to_char to_date cast but warnings are still coming....not sure why

[former_member298829]

Which datatypes are you using for the date columns at each stage of the processing?

[Former Member]

My source data is DATE data type..firstly I tried to map normally with DATE only...then made it Varchar used different conversion commands. but all the times i got the same warnings:-

5468    9572    RUN-053001    06-02-2015 09:57:19    Invalid value <Month: 31> for date <02013102>. Context: Column <>.

.. when I am spiting the tables in different Df s it it is also giving warnings but data is loading but when I am doing it in one df it is giving error .. Please help

[former_member298829]

You should be able to map straight through using the DATE datatype.

The error message you are getting suggests that DS is trying to convert a VARCHAR to a DATE using the date format YYYYMMDD, however the field looks like it has been offset as the year starts at the second character.

[Former Member]

Same thing is happening when I am trying to map as date field.

[former_member187605]

5468    9572    RUN-053001    06-02-2015 09:57:19    Invalid value <Month: 31> for date <02013102>. Context: Column <>.

02013102 is definitely not a date! It's most probably offset by one position. That's  the reason for the error message. Because mapping is correct and works for any other record without any conversion, as it should.

Can you try and figure out where that value comes from? How does it look like in ECC?