Date conversion in Universe

[Former Member]

Hello all,

   Am facing problem with converting date from one format to another for the custom Object in the Universe.(BO XI 3.1 version)

Presently date format  is  in     mm/dd/yyyy HH:MI:SS AM ,

And i want to convert the date format into  dd-Mmm-yyyy  format ,which is not happening now even after using the below query in custom object.

The parsing is 'OK' and also When clicked on Object properties --> properties -->Display --> It is showing data again in  mm/dd/yyyy HH:MI:SS AM  format.

to_date(to_char(BO_VIEW.FIELD1,'dd-Mon-yyyy'),'dd-Mon-yyyy')

I need to use this custom object as a prompt in Webi , Hence when report is run in Webi it should accept the date in the format dd-Mmm-yyyy only.

Kindly help on this.

Thanks in advance

Arjun

[Former Member]

Hi Arjun,

At Universe Level,  you can change the object as,

to_Char(CALENDAR_TABLE.CURR_DAY,'dd-MON-YYYY')

Make the object format as standard.

and Type as Char

when i make the same object as prompt in webi report, looks like below

Hope this would solve ur issue

Thanks,

Sneha Racharla

[Former Member]

have to do webi level, pls guide me regarding this

[Former Member]

Hello Vinay,

If your field is already of date type then use the below formula in WebI

=FormatDate(  <Date Field> ; "dd-Mmm-yyyy")

Else, if the field is of type String then first you will have to convert the string into date type and then apply the FormatDate function, use below formula for same

=FormatDate(  todate( <Date Field> ; "<exact format of string>") ; "dd-Mmm-yyyy" )

I hope for your case it should be like

=FormatDate(  todate( <Date Field> ; "mm/dd/yyyy HH:MI:SS AM") ; "dd-Mmm-yyyy" )

Hope this helps.

-Neeraj.

[Former Member]

THANKS NEERAJ. HOW TO USE PLACEHOLDER SYNTAX

SELECT

"EKBE_EBELN",

"EKBE_EBELP",

"EKBE_VBELN_ST",

"VEPO_VENUM",

"EQUI_ZZAVG_IMP",

"EQUI_ZZAVG_POWER",

"EQUI_ZZAVG_VMP",

"EQUI_ZZAVG_VOC",

"EQUI_ZZAVG_ISC",

"EQUI_MATNR",

"EQUI_SERNR",

"MAKT_MAKTX",

"MARA_HERKL",

"EKPO_DISUB_PSPNR"

FROM

"_SYS_BIC"."supplychain-dev/ZOTC_FLASH_DOWNLOAD"

('PLACEHOLDER' = ('$$VAR_DOC_NO$$',@Prompt(VAR_DOC_NO)),

'PLACEHOLDER' = ('$$VAR_DLV_NO$$',@Prompt(VAR_DLV_NO)),

'PLACEHOLDER' = ('$$VAR_BOX_ID$$',@Prompt(VAR_BOX_ID)),

'PLACEHOLDER' = ('$$VAR_SERNR$$',@Prompt(VAR_SERNR)),

'PLACEHOLDER' = ('$$VAR_WBS$$',@Prompt(VAR_WBS)))

GETTING SYNTAX ERROR PLS SUGGEST ITS VERY URGENT.

[Former Member]

If I pass the date

It should display week in the format

example from date 8/6/2012 to 9/9/2012

Week 32	August 6, 2012	August 12, 2012
Week 33	August 13, 2012	August 19, 2012
Week 34	August 20, 2012	August 26, 2012
Week 35	August 27, 2012	September 2, 2012
Week 36	September 3, 2012	September 9, 2012

[Former Member]

You need to apply the change and refresh the list of values to see the applied changes.

You also need to change the object format as mentioned above.

BO, however, has a default display format for LOVs (as you've encountered).

Have you tried bringing the object into a new report yet to see how it looks?

[Former Member]

Hi Mark/javed,

I tried all these ,while displaying in the webi report its displaying int he format "dd-Mmm-yyyy" which is as per my expectation ,

but same object is not working as a prompt

I need to accept the date from user in the format "dd-Mmm-yyyy" , which is a prompt.

it is accepting date as "mm/dd/yyyy hh:mm:ss" which is my exact problem .

Kindly tel me how to pass this date in "dd-Mmm-yyyy" format

Thanks in advance,

Arjun

[Former Member]

You'll need to set the box in the advanced properties section of the object definition tp 'dd-Mmm-yyyy'

[former_member195290]

Hi Arjun,

If you are using Oracle DB

Create a universe object as-

TO_DATE(@prompt('enter date:','A',,mono,free),'dd-Mmm-yyyy')

Please let me know your underlying DB if its different from Oracle.

Regards,

Javed

[Former Member]

Dear Javed,

  Am using Oracle 11g and in your syntax , i did not get the reference to an object.  Its just a prompt ?

My requirement is i need to change the date format for the object in Universe which is already present in the db.

For this reason i have created a custom object to achieve this.

Kindly help on this.

thanks in advance,

Arjun

[atul_kulkarni9]

Dear Bala,

Can you please tell meWhich Database you are using for Universe?

Bets Regards,

Atulm

[former_member225163]

You may try to set the object format in universe as mentioned below:

right click on object > click Object format > set the object format as "dd-Mmm-yyyy"

Hope this helps..

Thanks

Bala

[Former Member]

Dear Bala,

     Even the option you have suggested is already used and still the date format is not changing to the expected.

kindly let me know if any other methods are available to achieve this.

Thanks in advance,

Arjun

[former_member225163]

as you mentioned, date is in this format "mm/dd/yyyy HH:MI:SS AM" .. pls try below

to_date(to_char(BO_VIEW.FIELD1,'mm/dd/yyyy'),'dd-Mmm-yyyy')

[Former Member]

Dear Bala,

  If the syntax you have provided is used , then Parsing is OK but when Object properties --> properties -->Display  is clicked it will give an error as

Error during SQL execution :(DA0003)

is appearing.  

When i try to change format then every time parsing is OK , but shows the error as mentioned above.

Thanks in advance