DateTime @ Universe level

[former_member402770]

Hi,

Iam using BI IDT 4.1 and oracle 11g database can someone help me at universe level with syntax for converting date object of string type to datetime type to show the output like in below snapshot:

Appreciate your quick help,

Regards,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

Use this

to_char(to_date(Table name."Date",'DD/MM/YYYY'),'MON DD,YYYY HH:MI:SS PM')

See screenshot.

Amit

[former_member402770 · Accepted solution]

Hi Amit,

   The above screenshot formula looks very good in implementing to my current activity like a way could you please help me on the webi side assume say i donot want to do this conversion at the universe level.

Appreciate your quick help.

Thanks,

Dinya

[amitrathi239 · Accepted solution]

HI,

what is the date format of your date object at webi level and it's string or date type.

if datatype is date then at webi level-right click on cell->Formatnumber->Date/Times->select the predefined format.if your specfic format is not in default one then. click custom and add Mmm dd,yyyy

if data type is string then first covert the object in date.

Var date=todate([your object];"dd/MM/yyyy") according to your date object format replace the same format instead of dd/MM/yyyy.

After that either right click->formatnumber steps as mentioned above.

or other way

=formatdate([Var date];"Mmm dd,yyyy")

Amit

[former_member402770 · Accepted solution]

Hi Amit,

  Pls. see the below screenshot, Iam getting the below error. Iam using Oracle 11g and BO IDT 4.1

data dimension - string type:

there is no to_date and to_char func available rather i used:

toDate(@Select(Test\Date),'dd-MMM-yyyy')

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

Use this.

toDate(@Select(Test\Date))

Amit

[amitrathi239 · Accepted solution]

Hi,

After this if specfic format is required to display then follow these steps.

Right click on the date object->click on Create Display Format.

Select the format or even you can create custom format.

Amit

[nscheaffer]

Have you checked out documentation on the TO_DATE function?

Format Models

Oracle/PLSQL: TO_DATE Function

http://stackoverflow.com/questions/8366522/how-to-convert-a-string-date-to-date-format-in-oracle10g

Or am I missing your question?

Noel