Convert timestamp to webi date

[system]

Hi,
I have the current field [Timesheet Date] as TIMESTAMP format 2008-03-26 12:19:18.000
I want to convert this to a normal date format of dd/mm/yyyy in webi and subsequently want to use this field as a prompt when I run the report.

So when I run the report i dont want to see the original timestamp format but a normal date format.

For this I will need to make changes in the universe thus please advise on how I can acheive this.

Thanks and regards
A.B.

---

abhi900 (BOB member since 2008-08-12)

[system]

Hi,

Please follow the below link:

How Do I Trim Off TIME in a Date Object?

Group, I know this is a real simple one, but I’m all twisted around trying to get this thing worked out . Here is what I got: Oracle 8.17 BO 5.14 I have a LOV for a Date Dimension Object in a Universe called FULL_DATE. I need to have this Date Object show 10/24/2002 or ‘MM/DD/YYYY’ when the user is prompted in a report. However, the prompt LOV shows 10/24/2002 12:00:00AM. I need to supress the Time stamp part of the date that the LOV brings in from the Oracle Table. I’ve trie…

Regards,
Saurabh Gupta

---

saurabhg (BOB member since 2007-04-02)

[system]

Hello,

I know it is an old post, but my solution can still help people.

The problem is that in web intelligence the abbreviation of the month of April is “apr.” and for event date stamp thi is “apr”, so the dot is missing.

You can easily use the formula ToDate for the month of “may” (because there is no abbreviation for may) but for april you have to create a formula in wich you will replace “apr” by “apr.” in the event date stamp field.

Here is the formula, you need to take into account, the fact that :

• Some months do not have any abbreviation
• Some months have an abbreviation with 3 letters
• Some months have an abbreviation with 4 letters

I am french so the months are in french, you need to translate them in english (please see below)

My formula is for the event date stamp with this format: "avr 11 2016 1:21 PM"you may need to adapt the format.

FORMULA IN ENGLISH
=FormatDate(ToDate(Trim
(
If Trim(Left([Event DateStamp];4)) InList (“mars”;“mai”;“juin”;“août”)
Then Trim( Left([Event DateStamp];12) )
Else If Left([Event DateStamp];3) InList (“avr”;“oct”;“nov”;“déc”)
Then Trim (Left([Event DateStamp];3) +". “+Substr([Event DateStamp];4;9) )
Else If Left([Event DateStamp];4) InList (“janv”;“févr”;“juil”;“sept”)
Then Trim (Left([Event DateStamp];4) +”. "+Substr([Event DateStamp];5;8) )
);“mmm dd yyyy”);“dd/MM/yyyy”)

MONTHS
English: full name of the month in french (abbreviation in french)
January : Janvier (janv.)
February: Février (févr.)
March: Mars (mars)
April: Avril (avr.)
May : Mai (mai)
June: Juin (juin)
July: Juillet (juil.)
August: Août (août)
September: Septembre (sept.)
October: Octobre (oct.)
November: Novembre (nov.)
December: Décembre (déc)

FORMULA IN FRENCH

=FormatDate(ALaDate(SupprEspace
(
Si SupprEspace(Gauche([Event DateStamp];4)) DansListe (“mars”;“mai”;“juin”;“août”)
Alors SupprEspace( Gauche([Event DateStamp];12) )
Sinon Si Gauche([Event DateStamp];3) DansListe (“avr”;“oct”;“nov”;“déc”)
Alors SupprEspace (Gauche([Event DateStamp];3) +". “+SousChaîne([Event DateStamp];4;9) )
Sinon Si Gauche([Event DateStamp];4) DansListe (“janv”;“févr”;“juil”;“sept”)
Alors SupprEspace (Gauche([Event DateStamp];4) +”. "+SousChaîne([Event DateStamp];5;8) )
);“mmm dd yyyy”);“dd/MM/yyyy”)

Best Regards

Marie

---

Marie38 (BOB member since 2016-07-13)