Date format conversion issue in BW

[Former Member]

Hi Experts,

I have 2 custom date fields for which I'm getting the values from oracle database through DB Connect in MM/DD/YYYY format (e.g values 10/20/2007 and 3/5/2007). But I want to convert this date format into SAP BW internal format (YYYYMMDD). I've defined the above 2 date fields in BW with a data type DATS (8 digits length) and wrote a code in the TRs as below. Both these fields are defined as a character with a length of 10 in the oracle system.

data zresult(10) type C.

data : lv_day(2) type c,

lv_month(2) type c,

lv_year(4) type c.

zresult = tran_structure-spring_norm_date.

lv_day = zresult+3(2).

lv_month = zresult+0(2).

lv_year = zresult+6(4).

CONCATENATE lv_year lv_month lv_day into result.

But still the data load is getting failed due to wrong date format and error in the routine. It says the value for the characteristic 0DATE doesn't have 8 spaces. Is there anything wrong with the routine?

Could someone please share some inputs on this.

Thanks,

Krish

[Former Member]

data zresult(10) type c.

data : lv_day(2) type c,

lv_month(2) type c,

lv_year(4) type c.

zresult = tran_structure-spring_norm_date.

lv_day = zresult+3(2).

lv_month = zresult+0(2).

lv_year = zresult+6(4).

result(8)

CONCATENATE lv_year lv_month lv_day into result.

Include this logic and see if it works.

If it doesn't try declaring result as type sy-datum

Edited by: BG on Apr 25, 2008 10:44 AM

[Former Member]

hi Krish,

Use this code as example.

DATA: get_date TYPE sy-datum,

s1(2) TYPE c,

s2(2) TYPE c,

s3(4) TYPE c,

lv_length TYPE i,

lv_length1 TYPE i.

SPLIT SOURCE_FIELDS-/bic/zbrdate2 AT '/' INTO s1 s2 s3.

lv_length = STRLEN( s1 ).

IF lv_length = 1.

CONCATENATE '0' s1 INTO s1.

ENDIF.

lv_length1 = STRLEN( s2 ).

IF lv_length1 = 1.

CONCATENATE '0' s2 INTO s2.

ENDIF.

CONCATENATE s3 s2 s1 INTO get_date.

RESULT = get_date.

[Former Member]

Hi Narasimha,

Thanks a lot for your prompt response and inputs. I used this code as below in my TR for the date field. But its still giving an error message saying "Value '20072109' of characteristic 0DATE is not possible and error when assigning SID". The value what I can see in the source system (oracle table) and in PSA for this record is '9/21/2007'. Am I still missing something here?

DATA: get_date TYPE sy-datum,

s1(2) TYPE c,

s2(2) TYPE c,

s3(4) TYPE c,

lv_length TYPE i,

lv_length1 TYPE i.

SPLIT TRAN_STRUCTURE-FALL_NORM_DATE AT '/' INTO s1 s2 s3.

lv_length = STRLEN( s1 ).

IF lv_length = 1.

CONCATENATE '0' s1 INTO s1.

ENDIF.

lv_length1 = STRLEN( s2 ).

IF lv_length1 = 1.

CONCATENATE '0' s2 INTO s2.

ENDIF.

CONCATENATE s3 s2 s1 INTO get_date.

RESULT = get_date.

Could you please help me on this?

FYI, I have defined my date field in ODS with the data type DATS in order to display the output in YYYYMMDD format.

Thanks,

Krish

[Former Member]

Now I tried to change the concatenation statement as below.

CONCATENATE s3 s1 s2 INTO get_date.

This time there was no error message but the format in ODS active table for this field is still wrong.

It's showing as 09/21/2007. The value in the source system table and in the PSA is '9/21/2007'.

How do I get the YYYYMMDD format (20070921) in my date field in ODS?

Any help on this would be greatly appreciated.

[mathew_muthalaly]

Hi,

Suggest that you concatenate into another variable and say 'result = variable'.

BR/

Mathew.