Skip to Content

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

To report this post you need to login first.

14 Comments

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

  1. Manish Danani

    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

    (0) 
  2. D J

    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

    (0) 
      1. D J

        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

        (0) 
  3. Kousik Das

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

    (0) 
  4. 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

    (0) 
        1. Asgar Ali MK 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

          (0) 
  5. Narendra Kakarla

    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

    (0) 

Leave a Reply