# Convertion functions in BODS with Examples_Part1

Conversion functions in BODS_Part1

There is different conversion function which used to convert one form of data into another format. The BODS support most of basic conversion functions.

Here, I will explain the syntax and examples for those conversion functions.

1. To_char

The to_char function used to convert number or date value into string format.

This function will help you to convert a number or date to string.

The syntax for this function is given below with examples.

To_char(number or date, format)

Number to string.

e.g.

• to_char(123,’9999’)= ’123’

• to_char(123,’09999’)=  ‘0123’
• to_char(31,’xx’) = ‘1f ‘ —-> string contains hexadecimal integer.
• to_char(31,’xx’)= ’37’ —-> string contains octal integer

Date to string.

e.g.

• to_char(sysdate(),’MM’)= ‘06’ –> month number of the date
• to_char(sysdate(),’MON’)= ‘JUN’  —> Month name with 3 letter like ‘JUN’
• to_char(sysdate(),’MONTH’)= = ‘JUNE ‘ —-> Month name  like ‘JUNE’.
• to_char(sysdate(),’DD’)=’03’ —-> Day part of the date
• to_char(sysdate(),’YY’)=’13’—–> 2 digit YEAR
• to_char(sysdate(),’YYYY’)=’2013’—-> 4 digit YEAR
• to_char(sysdate(),’HH’)=’12’—-> 2 digit Hour
• to_char(sysdate(),’MI’)=’13’—-> 2 digit Minute
• to_char(sysdate(),’SS’)=’13’—-> 2 digit Second
• to_char(sysdate(),’FF’)=’286959000’—-> Sub second

2. Intervel_to_char

This function is to convert an interval to string. This will help you find out the date difference between two dates in different format. Find below given examples and syntax for this function.

The syntax for this function is given below.

Interval_to_char(interval, interval type)

\$start_date=2013.01.01 00:00:00

sysdate() = 2013.06.05 17:31:54

Interval_to_char(\$start_date- sysdate(),’D’)=’155’

This statement will give the number of days between given interval. Similarly,

Interval type:     M for minutes

S for Seconds

H for hours

3. Julian_To_Date

This will convert Julian value to date value.

The syntax for this function is given below.

Julian_to_date(Julian) return date

Julian_to_date(2,451,545) = 2000.01.01 00:00:00

https://en.wikipedia.org/wiki/Julian_day

4. Num_to_Intervel

This function will help you to convert an number into interval value.

The syntax and examples are given below.

If sysdate()=2013.06.05 17:52:21

E.g. sysdate()+num_to_intervel(12,’D’)=2013.06.17 17:52:21(Adding 12 days to given date)

sysdate()+num_to_intervel(1,’H’)=2013.06.05 18:52:21(Adding 1 hour to given date)

Similarly, the number can convert into minutes (M) and seconds(S).

Regards

Asgar

### Assigned Tags

You must be Logged on to comment or reply to a post.

This is really a nice and informative stuff, Thanks for putting it here, Asgar.

Best | Deepak.

Asgar Ali MK
Blog Post Author

thank you

Hi Asgar,

I am new to the BODS area and was wondering if there is a document that lists all the available functions along with the syntax of arguments and examples as a reference for new comers?

Thanks.

Regards,

Manish

Hi Asgar,

Really Appreciative!!! these functions are really helpful.

Regards,

DJ

Hello Asgar,

As I am new to SAP BODS, Previously I was working in informatica, their is a INSTRING function can you please tell me what is here in SAP BODS.

DJ

Hi Deepak,

In BODS you have to INDEX function instead of INSTR function.

Thanks,

Deepa

Hi Deepa(sap bods),

Thanks a lot for the reply i will try it and let you know if i get successful in doing what i want.

Regards,

DJ

In case of Adding days,one BODS function is availavle "ADD_DAYS" which can add number of days in integer with sysdate.

HI Asgar,

Input

Atlas_Id                                                Sr_id
X/P01/ZOOMAR UK                      Zoomarine -One of itâ€™s Kind%Ticket Only

Output
Field1           Field2                     Filed3                                              Sr_id
X                     PO1                ZOOMAR UK                Zoomarine One of its Kind Ticket Only

Regards
Varsha

Asgar Ali MK
Blog Post Author

Hi Varsh,

Regards

Asgar

Hey Asgar,It's not work

Asgar Ali MK
Blog Post Author

Hi Varsha,

Use below mapping

Field1 = word_ext( Atlas_Id,1,'/')

Field2 = word_ext( Atlas_Id,2,'/')

Field3 = word_ext( Atlas_Id,3,'/')

Regards

Asgar

Hi,Good Morning,Thanks

Hi Asgar,

I have a temp table in this data loaded with sydate (), field name loaddate as data type date time

after loading all the data i want the maximum time stamp(ex:max(loadate)) in a variable data type date time .how to achieve this.

date time like 2016.04.20 14:40:01.833000000 in this format

Thanks,

naren