on 06-16-2015 7:22 AM
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.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
simply use trunc function in oracle.
the result of trunc(datetime) would be date only in oracle.
Thanks,
SB
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
80 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.