Skip to Content

                                            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.

Please refer below examples.

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.

3. LOAD_TO_XML

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:

Load_to_XML(nested_table name,Schema_dtd_name,enable_validation,xml_header,replace_string_nulls,is_top_level_element,max size)

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

To report this post you need to login first.

4 Comments

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

  1. Emiliano Bonizzi

    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

    (0) 
    1. Abinesh P N S

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

      If that is your question, Please use the below code in your BODS WHERE clause,

       

      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

      (0) 

Leave a Reply