Formatting a Date dimension

[Former Member]

Can you change the format of a date dimension.  I have a date coming in from my data source in the mm/dd/yyyy format and  I need it to be mm/yy.  I tried FormatDate function but was not successful.  Any suggestions?

Susan

[nscheaffer · Accepted solution]

Is your date dimension actually a date data type or is it string data type?  Click and hover over the date object in the Available Objects pane to find out.

Here some links about converting dates in string data types to actual dates and the numerous ways to format a date.

How to convert a string prompt into a date in WebIntelligence | Business Objects Blog

Web Intelligence Date Formats | Business Objects Blog

Hope this helps,

Noel

[former_member640919 · Accepted solution]

Try this

=ToDate([Date in String]; "INPUT_DATE_TIME")

[Former Member · Accepted solution]

Hi Noel,

Thank you for your response.It says it's a "date".  It is not a string.  It is information from a table.

Susan

[Former Member · Accepted solution]

Hi Ms. Jyothy d,

Thank you for your reposne but the date is not a string.  It is data from a table but the data type is a date.

Susan

[nscheaffer · Accepted solution]

If it is a date I think we are all making this more complicated than it needs to be.  I think you just need to create a custom format of MM'/'yy.  To do that do the following...

1. Click on the date column in your report that you want to format.
2. Click on the "Format" tab in the top row.
3. Click on the "Numbers" tab in the second row.
4. Click "Date/Time" and choose a sample format that is close to what you want to seed the custom format you are about to create.
5. Click "Custom".
6. Type MM'/'yy in the the Date/Time field.
7. Click "Add".
8. Click "OK".

You have to be using the Java Applet or Rich Desktop versions of Web Intelligence to be able to create a custom format.  Once you have done so it can be used in the HTML version of Web Intelligence; it just can't be created there.

Does that help?

Noel

[Former Member · Accepted solution]

Hi Noel,

Thanks that worked!!

Susan

[Former Member]

Hi Susam,

If you work with a universe and you use a DATE object.

You can :

1/ Open your universe in designer

2/ Click right on DATE object

3/ Go to Object format => date  : here you can specify date format that you like.

I hope this can help

Kind Regards,

Ameni

[Former Member]

Hi Ameni,

Thank you for your comment but I am not the administrator on the universe.  Other people use the universe so I can't change the format of the date in the universe.

Susan

[former_member4998]

Create Variable

V_Date= =FormatDate(ToDate([Maturity Date];"MM/DD/YYYY HH:mm:ss A");"MM'/'YY")

It it’s not working

V_Date= =FormatDate(ToDate(Place your field);"MM/DD/YYYY HH:mm:ss A");"MM'/'YY")

Remove field again and check

[former_member4998]

HI

Please try below..

=FormatDate(ToDate(YOUR DARE OBJECT);"MM/dd/yyyy HH:mm:ss A");"MM'-'yy")

=FormatDate(ToDate(YOUR DARE OBJECT);"MM/dd/yyyy HH:mm:ss A");"MM'-'yy'")

[Former Member]

Thank you for your reply.  I think this is a good solution.  Here's what happened:

In my report I pulled in my date object and then used the formula editor and typed in:

=FormatDate(ToDate([Maturity Date];"MM/DD/YYYY HH:mm:ss A");"MM'/'YY")

I got #Error in my data column.  Any ideas?

Susan

[former_member190895]

If FormatDate is not working then below is the workaround i could suggest.

a1=FormatDate([Date Obejcts];"dd/mm/yy")// This will bring date in 10/03/14

a2=Right([a1];4;lenth([a1]))// This will extract mm/yy  i.e 03/14

[Former Member]

Hi Mantu,

This looks like it would work but I don't understand how to enter this as a formula.  Can you put it together as how I should enter this in the formula editor,please?

Susan

[Former Member]

Mantu,

I tried this several times and got syntax errors.  I think that you have a spelling error is your response as well "lenth" probably should be"length?

Susan

[former_member190895]

Hi Susan,

Yes its length only sorry for type error. As i have suggested create two variable as a1 and a2. Assign them the formula which i have shown above. Also let me know if any error with screen-shot.