cancel
Showing results for 
Search instead for 
Did you mean: 

Date Time ( 29/06/2015 hh:mm:ss AM )to Date (29 Jun 2015) format in Universe

Former Member
0 Kudos

Hi All,

Is there a way to convert DateTime ( 29/06/2015 hh:mm:ss A ) to 29 Jun 2015 in universe.

I am aware of Object format in universe. But I want other method of converting to Date ( dd Mmm yyyy ) format in the universe.

It would be great if the answer is provided for both Oracle and Sql Server DB's.

The main agenda behind my requirement is when I am exporting the wei report to excel the date is showing as Custom when defined using object format in universe and text format when using formatDate in webi.

so, I need date format (dd Mmm yyyy) iin universe itself.

Thanks,

Santhosh.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Exporting dates to Excel is pot luck at best.

Let's start with the basics. Trimming time off a datetime:

Oracle: trunc(table.datetime_column)

SQL Server: cast(table.datetime_column as date)

If you want to have a date of forum 13 Dec 2014 then you'll need to format it as string or change the format of the object. You can change the display format of the object but this will only change the format at report level for new reports; existing reports will keep their existing date format.

Therefore you may be better off creating a date string using business layer logic to convert your date fild to a character field (the equivalent of FormatDate([Date Object];"dd Mmm yyyy") in Webi.

SAP and Microsoft will both argue  that it's not their issue to fix, which doesn't help us!

Answers (2)

Answers (2)

Former Member
0 Kudos

simply use trunc function in oracle.

the result of trunc(datetime)  would be date only in oracle.

Thanks,

SB

Former Member
0 Kudos

Hi Swapnil

Thanks for the reply. I am able to get the output as "12/31/2013". can you help me to change the format as 31 Dec 2013 in the universe itself but not using "object format" and not even in webi level.

Thanks,

Santhosh

Former Member
0 Kudos

you can simple use

to_char([date],'dd mon yyyy')

Thanks,

SB

Former Member
0 Kudos

On similar notes have the above requirement only :

Swapnil If we use to char then the date will be converted to Character .

We want it in date Format only

I tried

todate(tochar( dateobject,'DD-Mon-YYYY HH:MI:SS'),'DD-Mon-YYYY HH:MI:SS')

This Pasres Ok but the value is not coming as expected they are coming as mm/dd/yyyy.

However If i use Only to char( without to date) the result is displayed as expected Only thing is we don't want it as character data type.

Former Member
0 Kudos

Yes Kultar. You got it. Still searching for the desired output.

Former Member
0 Kudos

hi

you are using the date on report somewhere?

|Since you are getting your required output why are you more worried about the datatype?

Can you explain little more on this


Former Member
0 Kudos

Hi Swapnil.

Yes, I am using Date in the report. but when am exporting the report to excel, the date is been shown as text format when we use format date and custom format when using object format in the universe. so, I am trying for some other method.

Is there anything I need to provide.

ThanQ

Santhosh.

Former Member
0 Kudos

at first apply filter on report using my solution as mentioned above.

Then you can choose custom format for an object report level which will convert your object from string to date.(you can use FormatDate command)

later export report to excel which in will be date formatted.

I think you main purpose of getting data filter is fulfilled using my solution.

Please let me know in case of any misunderstading  by me or you require some other alternate solution.

Thanks,

SB

Former Member
0 Kudos

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

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

Hope this helps..

Thanks

Nishant

Former Member
0 Kudos

Hi Nishanth, I am aware of it and tried already too, but still it is showing custom typed in excel. so, I am looking for another method of getting the date type.

Thanks S.

amitrathi239
Active Contributor
0 Kudos

try with this.

to_date(table.object,'dd Mmm yyyy')

I haven't tested the values but object parsing is ok.

Amit

Former Member
0 Kudos

Hi Amit,

Its been showing showing exceptions as: not recognizing the date format.

Exception: DBD, ORA-01821: date format not recognized

State: N/A.

It is not displaying the data.

Thanks S.

Former Member
0 Kudos

Hello Santhosh,

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.

Nishant

Former Member
0 Kudos

Hi Nishanth,
I think these all are for webi but not for Universe level. I have checked it is throwing "Invalid identifier"

Former Member
0 Kudos

Yes , You can apply these formula on webi Level.

Former Member
0 Kudos

by following that format, I am unable to get date format in excel. so trying to get date type directly from universe with "dd Mmm yyyy" even not using object format in unv.

Former Member
0 Kudos

Instead of using  format DD/Mmm/YYYY use DD/Mon/YYYY.

Hope this will work.

Regards,

Nishant

Former Member
0 Kudos

sorry. nishant. not matching.