Incorrect Date Format coming from Universe to Webi

[elizabethwalker]

We are on Universe Designer version 12.1.0.882 and the Universe is combining tables from ECC6 tables with SQLServer as the backend database.

So, here's my scenario. I have brought over many tables into this particular universe. My experience is that when a table is included from SAP ECC6, the Date fields (actually specified as dates in ECC6) come over as a character field in the format of yyyymmdd with "00000000" coming over on empty/null fields. I have used the following code to convert the field back to a date in Universe Designer:

case when t50.AFKO.GSTRI='00000000' then '' else convert(DATE, t50.AFKO.GSTRI,101) end

which is supposed to put a null value in the place of '00000000' and then convert all other dates in the 101 format. Code 101 is the Date style code for the US standard mm/dd/yyyy and is the required format for my customers. The code parses correctly but when it gets into a webi report, the fields that were supposed to be null come over as 1/1/1900 and the date field is not converted to mm/dd/yyyy as the code 101 is supposed to do in the convert statement in the Universe. So, my problem is...every date field has to be manually changed to the correct format on every WEBI report that is using the date fields from this universe. Am I doing the conversion wrong? Is there a known issue with converting to the code 101 format and displaying in WEBI? Is there a place within WEBI admin to specify the default date format for all tables coming into WEBI?

I am relatively new to SQLServer lingo and more familiar with Oracle lingo so it may just be something wrong with my code. This problem has taken up a significant amount of my time and your assistance or any suggestions would be greatly appreciated.

[Former Member]

Hello Elizabeth,

Which Date field are you finding it difficult to format?

Is is the one from ECC 6 or SQL Server?

If it is from the SQL server, try using the below command:

If your Date is stored as Char in SQL Server, first convert it to a date and then you can format it.

CONVERT(VARCHAR(10),CONVERT(DATE,<TABLENAME>.<DATE>),101)

Let me know if this helps.