Type String to DateTime

[former_member402770]

Hi Experts,

  I need to change the datatype in  IDT(information design tool) my source is Oracle. From the Date Dimension object, i am getting the date formate as 20-SEP-2015 which is of  type string i need to convert to same date format with only datetype change as datetime@the Universe Level

I would need Date Dimension object as datetime type rather String type

Using Oracle 11g and IDT 4.1

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

Use todate function to convert in the date.

toDate(@Select(Test\Date))

Amit

[former_member402770 · Accepted solution]

Hi Amit,

Strange issue here the Date dimension object is of string type from oracle database. As from your above post,

Created new dimension object named as 'Date obj" of type date i used something like below, parsing validated successfully at the universe level.

toDate(convert(@Select(Test\Date obj),Varchar))

At webi level throws below error, but it is running for few fiscal periods prompts values:

Appreciate your quick help, should we have to use  any cast(Test.Date as datetime) function.

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

Accroding to the error might be some non-numeric values are coming this date object values.Might be the reason it is not working when Alpha numeric values are coming in the prompt.

Create one webi report and drag only date object without any prompt and see how values are coming in the report.Check if any alpha numeric values are coming or not?

other part can you try without convert function in the universe object.

toDate(@Select(Test\Date obj))

Amit

[former_member402770 · Accepted solution]

Hi Amit,

  Just checked the data values of date dimension object. Below is:

the above date dimension object is of type string, i would need this dimension object to be of type date at universe or report level.

Thanks,

Dinya

[amitrathi239 · Accepted solution]

Hi,

What about if you will convert  the string object to date and select the datatype date at universe object level.

toDate(@Select(Test\Date obj))

check this if it is working.if yes then share the data screenshot. similar to  your previous data.

Amit

[former_member402770]

Hi Amit,

  Below the issue. Date type set as date only...

In Webi:

Thanks,

Dinya

[amitrathi239]

Hi,

Try with below steps.

Open the data Foundation layer.

Right click on the table->insert  calculated column.

Enter the name of the column.

Checked the checkbox "use database specific SQL option.

use to_date oracle function to convert string in the date.

In attached screenshot i have use static date but you can double click on the column which you want to convert from the columns window.

Save the DF.

Use this newly created object in the business layer and see if it will work.

Amit

[former_member402770]

Hi Amit,

  Iam just trying out your steps. Could you please let me know why the calculated column data values are repeating will have to ignore this and proceed. Any idea.

Thanks,

Dinya

[amitrathi239]

Hi,

Can you first try in webi if prompt condition is working or not.See if values are duplicating there or not.

can you share the date object screenshot in the business layer.

Where you have checked these values?

Amit

[former_member402770]

Amit,

  Perfect great it is working like charm. Many thanks for your quick help.

Thanks,

Dinya

[former_member402770]

Amit,

  One clarification from your screenshot, i used to_date(tablename.column) and it has parsed succesfully Checked the checkbox "use database specific SQL option.

Checked the syntax of to_date($1,$2) function has 2 parameters but i have provided only one parameter like to_date(tablename.column) ? Is that ok?

Thanks,

Dinya

[amitrathi239]

Hi,

To_date function is more specific to oracle. todate is generic which is available for  most of the databases.

in your case when option is available to use oracle function then you have to use those first.Like add_months function is only available with oracle database.

Amit

[amitrathi239]

Hi,

"use database specific SQL option" option is available only with multi source universe when you are connecting with multiple data sources.Otherwise you will find it's disabled.

yes you can use  one parameters in the to_date function. If you are not sure then when you will click on validate you will get the error.Universe designer is enough smart to tell you which syntax will work and which will not work .

Amit