Formatting date

[Former Member]

Hi,

From a BI Query I receive a date which is displayed in a table. In the table the date is displayed as follows:

01.03.2008
31.12.2008

I want it to be displayed as March 01, 2008 and December 12, 2008.

I have used the following formula in the custom text formatting of the cell of the table:

DSTR(@Actual_Date_to_ext_key,"MON DD, YYYY")

Strangely the date is now displayed as

jan 03, 2008
Jul 12, 2008

What am I doing wrong? BTW: The field in the table isof data type text and I can not change it to Date. I have already tried to add a new field with data type date, but it doesn't display the date delivered by the query.

Thanks in advance for your help and best regards,

Jan Laros

[Former Member · Accepted solution]

Hi,

referring to the SAP documentation :

http://help.sap.com/saphelp_nw70/helpdata/EN/5b/7bbd21fcb44a74856bb4bcf8697010/frameset.htm

the only available month mask that exists are :

M

Months as 1-12

MM

Months as 01-12

MON

Months as Jan-Dec

so I'm not sure what you want is possible or not.

One thing you can do is a string replace of your DSTR(....) result, like working with the REPLACE function.

Fabien.

[Former Member · Accepted solution]

Hi Fabien,

Once again thank you for your reply. The standard as you mention is fine for me, so Mar 01, 2008 would be fine. However, the formula used is delivering/calculating the wrong output. We get Jan 03, 2008. So it's switching months and days.

We are on NW 7.0 SP13 by the way.

Best regards,

Jan

Edit: Could this actually be an SP level problem? As stated in this topic:

Edited by: Jan Laros on Mar 18, 2009 9:37 AM

[Former Member · Accepted solution]

Hi Jan,

It is working fine in my system. I'm using SP15. There is an option is available in 'Formatting tab' of date field's properties. I changed it as 'Jan 31,2001' and it shows like what we need. Upgrade your SP and try it.

Regards

Basheer

[Former Member · Accepted solution]

You are right once again, Basheer. It seems that day and month are switched in SP13. I had it tested on an SP15 system and it worked fine. Thanks all!