# Conversion functions in BODS with Examples_2

Conversion functions in BODS_Part2

In this section I will explain mainly three conversion function in BO data service. They are mainly CAST, To_Date and load_to_XML.

1. CAST

Cast function used to explicitly convert an expression in one data type to another data type. The syntax for this function is given below.

CAST(Expression, data type) return data type.

The expression is the input which will be convert to target data type and the data type should be in build data type .

Syntax for different data type is given below .Please see below given table.

 Data Type Syntax Varchar Cast(expression,’ varchar(length)’) decimal Cast(expression,’decimal(Precision,scale)’) Integer Cast(expression,’int’) Real Cast(expression,’real’) Double Cast(expression,’double’) Timestamp Cast(expression,’timestamp’) Datetime Cast(expression,’datetime’) Date Cast(expression,’date’) Time Cast(expression,’time’) Intervel Cast(expression,’intervel’)

Examples for those functions are given below.

 Input Expression Source and target Datatype Ouput CAST(‘12.20’,’INT’) Varchar to int 12 CAST(‘12.20’,’DECIMAL(3,1)’) Varchar to decimal 12.12 CAST(12.20,’REAL’) Decimal to real 12.200000 CAST(12.20,’DOUBLE’) Decimel to double 12.200000 CAST(12.20,’VARCHAR(4)’) Decimel to varchar 12.20 Cast(‘2012.01.01’,’date’) Varchar to date 2012.01.01 Cast(‘2012.01.01’,’datetime’) Varchar to datetime 2012.01.01 00:00:00 Cast(‘2012.01.01 12:01:12’,’timestamp’) Datetime to timestamp 2012.01.01 12.01.12.000000000 Cast(‘sysdate(),’time’) Datetime to time 12:21:00 Cast(12.121239,’intervel’) Integer  to interval 12.121239

This table shows all the data type conversion valid for the cast functions.

 From-To Varchar Date DateTime Integer Real Double Interval Timestamp Time Decimal Varchar x x x x x x x x x x Date x x x x DateTime x x x x x Integer x x x x x x Real x x x x x x Double x x x x x x Decimal x x x x x x Interval x x x x x x Timestamp x x x x x Time x x x x x x

From above table you will get which one data type can be convert to another one and which all are can’t convert to other data type.

2. TO_DATE

The to_date function used to convert a string to date value. This function converts a string into date format.

The string can also convert into datetime and time value format.

If the input string has more characters than the format string, the extra characters in the input string will be ignored and will be initialized to the default value.

For example, to_date(‘10.02.2007 10:12:12, ‘DD.MM.YYYY’) will convert the date to 10.02.2007 00.00.00. The time part in the input string will be ignored and initialized to 0.

The syntax of the function is given below.

To_date(string,format) returns date ,datetime or time

 FORMATS DD Day of the Month(2 Digit) MM Month Number(2 Digit) MONTH Full Month Name MON Month name(3 char) YY Year(2 Digit) YYYY Year(4 Digit) HH24 Hour(2 Digit) MI Minutes(2 Digit) SS Seconds(2 Digit) FF Sub Seconds(Upto 9 Digit)

The examples for this function are given below. A string can be converted into date value based on the above given format.

 Function Result To_date(‘jun 18,2012’,’MON DD,YYYY’) 2012.06.18 00:00:00 To_date(‘1March1900’,’DDMONTHYYYY’) 1900.03.01 00:00:00 To_date(’12-12-01 13:12’,’MM-YY-DD HH24:MM’) 2012.12.01 12:00 To_date(‘2012Jan01’,’YYYYMONDD’) 2012.01.01 00:00:00

These are examples for the string to date conversion .you can do any conversion from string to date based on the format given.

This function convert an NRDT(Nested Relational Data Model) into XML and place it as a single column during the data load.

If the function fails during XML conversion then data services will return NULL value.

The Syntax for this is:

default is UTF-8

 nested_table_name Name of NRDM table. Schema_dtd_name Name of DTD or XML Schema enable_validation Enter 1 to validate and 0 disable validation xml_header replace_string_nulls Replace null with this value is_top_level_element 1 or 0 max size Expected size of the XML.

Regards

Asgar

### Assigned Tags

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

Why CAST not work on source server side (not push down) ?

But TO_DATE work fine.

Source Server is Sybase IQ

In my system the "decimal to varchar" is not returning 12.20

but 12,20

Anyone knows which settings to change the decimal separator?

DS 4.2 SP5 P1

in another server with previuos DS 4.1 is returning 12.20

i am new top BODS i am looking to extract rolling 12 months based on ERDAT.

I'm quite unclear with your question, what I have understood is you are extracting last 12 months data for field ERDAT.

to_char(to_date(Query.ERDAT,'YYYY.MM.DD'),'YYYY') >= to_char(to_date(sysdate(),'YYYY.MM.DD'),'YYYY') - 1 and

to_char(to_date(Query.ERDAT,'YYYY.MM.DD'),'MM') >= to_char(to_date(sysdate(),'YYYY.MM.DD'),'MM') - 12

Hi Asgar

I have converted the date to varchar for mat DD-MON-YYYY, but now I need to compare the two dates from different table and select which ever is higher date. Example ifthenelse(D1 < D2,D2,D1).

How can i do this? Date being in varchar will identify for greater than sign?

ifthenelse(to_date(D1,' DD-MON-YYYY') < to_date(D2,' DD-MON-YYYY'),D2,D1).

Hi Dirk

Thanks for the response. But to_date is not converting to date. I tried it during creating the view but I get the value as null. My exact query is

to_date(Activation_dt.Max_Cont_Activation_dt_Party,'DD.MM.YYYY HH24:MI:SS')

I tried all formats like 'YYYY.MM.DD HH24:MI:SS'

'YYYY.MM.DD'

'DD.MM.YYYY'

'DD-MM-YYY'

'DD-MON-YYYY'

to_date does convert to date. The name says it all, doesn’t it?

What’s the data type of Activation_dt.Max_Cont_Activation_dt_Party? What does the content of that field look like?

Dirk,

Regards,

Pryianka.

to_char(sysdate(),'YYYY.MM.DD HH:MI:SS.FF')

Does anyone know how to convert a string to boolean in SAP Data services?

The format 'YYYY.MM.DD HH:MI:SS.FF' gives 9 digits of sub-second precision.

In Oracle, I can use the format 'YYYY.MM.DD HH:MI:SS.FF3' to give just 3 sub-second digits, but in Data Services 14.2, this just appends a '3' to the end of the nine digits.

Is there a way to control the number of sub-second digits in the text format?

Hi Guys...

I have a problem..

Import table SAP ok.

EG.: CAWN.

This field ATFLV is datatype is FLOAT

Value: -2,7300000000000E+02

and using to ETL Query, DOUBLE, and update value for this: -273

Can someone help me ???