Skip to Content
Author's profile photo Asgar Ali MK

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

Please refer below link to get more information about Julian day.

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

      14 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Deepak K Gupta
      Deepak K Gupta

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

       

       

      Best | Deepak.

      Author's profile photo Asgar Ali MK
      Asgar Ali MK
      Blog Post Author

      thank you

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      Hi Asgar,

       

           Really Appreciative!!! these functions are really helpful.

       

      Regards,

      DJ

      Author's profile photo Former Member
      Former Member

      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.

       

      Thanks In Advance,

      DJ

      Author's profile photo Former Member
      Former Member

      Hi Deepak,

       

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

       

      Thanks,

      Deepa

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Kousik Das
      Kousik Das

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

      Author's profile photo Varsha Sonik
      Varsha Sonik

      HI Asgar,

       

      Please help me out.
      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

      Author's profile photo Asgar Ali MK
      Asgar Ali MK
      Blog Post Author

      Hi Varsh,

       

      Please use word_ext() function

       

      Regards

      Asgar

      Author's profile photo Varsha Sonik
      Varsha Sonik

      Hey Asgar,It's not work

      Author's profile photo Asgar Ali MK
      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

      Author's profile photo Varsha Sonik
      Varsha Sonik

      Hi,Good Morning,Thanks

      Author's profile photo Former Member
      Former Member

      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