Month Full Name to Month number conversion in SAP BODS

[Former Member]

Hi Experts,

I am working on SAP BODS. I have a source table which has few fields Time_Key, Day, Month, Year. The month field holds full name of month in each record (Like January, February etc...). I want to map all the source fields to an output template.

Mapping :

Table1 : Source Table

Table2 : Target Template

Time_Key, day, year -> As It Is to Template

Month - > Month number only (Please compare Source Month & Target Fields)

Could you please help me on how to write a corresponding number in target template based on month Full Name in source Month Field?

Thanks in Advance.

--Adithya

[ruprekhadeka123]

Could you plz be clear in requirement that what are your source fields and what are the target fields where you want information ?

You can take as below:

Source Fields (SF) Target fields(TF)

SF1 SF2 SF3 TF1 TF2

Because you need to write separate logic for all.

[Nawfal]

This function I suggested will work for you: month(to_date(source_month_field, 'mon')).

Add it to the field mapping in your query transform and try it. It should work and you don't require full date .

Thanks

[Julian_Riegel · Accepted solution]

Hi there,

You could use multiple approaches to achieve the desired result. The easiest probably would be using a decode function such as:

decode ((MONTH = 'January'), '1',
(MONTH = 'February'), '2',
(MONTH = 'March'), '3',
(MONTH = 'April'), '4',
(MONTH = 'May'), '5',
(MONTH = 'June'), '6',
(MONTH = 'July'), '7',
(MONTH = 'August'), '8',
(MONTH = 'September'), '9',
(MONTH = 'October'), '10',
(MONTH = 'November'), '11',
(MONTH = 'December'), '12',
'Invalid_Source_Value')

Alternatively you could generate yourself an additional field where you concatenate the three input columns and apply the to_date function, such as:

To_date(Day||Month||Year,’DDMONTHYYYY’)

Afterwards, apply the month() function on that concatenated field to retrieve only the months number or do it in one step like:

month(To_date(Day||Month||Year,’DDMONTHYYYY’))

or as nawfal.tazi1 suggested in his above answer - that should work aswell indeed.

regards

Julian