Date issue with Webi Report

[Former Member]

I am trying to put a filter in my Webi report to capture last 90 days and struggling.  

I am on Business Object 3.1 and have built a universe using ODBC to connect to my ECC R3 system.   I'm using a table called VBAK and the field ERDAT (creation date).   In Universe the properties of this object says "Character"  and when I display the values I get YYYYMMDD which I had assumed it was a string BUT that is not the case,  because I can wrap a DATEADD function on it and it will give me results. I'm trying to get the data type to match the 2 date objects I have created in my Universe.  Those are:  I have an object "Current Day" I used GETDATE() for that THEN I created another object called "90 days ago" I used DATEADD(DAY, -90, GetDate()).   The format in the Universe when I view both of these LOV's  look to be in a DATETIME format but in the Webi is shows as a MM/DD/YY.  

So I'm trying to get all data types to work together so I can use my 2 new objects in the "create date" filter in my Webi report.

[Former Member · Accepted solution]

FIGURED IT OUT!   convert(datetime, cast(prd.VBAK.ERDAT as datetime), 101)  did the trick!

Thanks!

Jen

[Former Member · Accepted solution]

Hi Jen

Nice to hear that!!!

Regards

Sandeep

[Former Member]

Hi Jennifer

Please see http://www.w3schools.com/sql/func_convert.asp

It may help.

Regards

Sandeep

[Former Member]

Thank you Sandeep but I have been on w3schools for the last couple days. Nothing works that I try. What would be the proper convert statement for an object that sql already believes to be a date (however looks like a string) and its in a YYYYMMDD format?  How can I convert that to MM/DD/YY?

Thank you for your help.

Jen

[former_member189638]

What happens when you change the type from Character to Date?

[Former Member]

I don't change it to Char to Date because its already a date but appears to be a char.  Object properties SAY its a character but I can do DATEADD(day, 0, prd.VBAK.ERDAT) and it will parse and changes to the format below in the pic when I display the LOV so I think the SQL server already thinks its a date.

then I format the object in the Universe to a MM/DD/YY format and Export it.   But when drag it in my webi report and compare to the original YYYYMMDD object its like 1 - 2 days off.  

I'm sure I'm doing something wrong because I'm very new to this.   Has anyone ever built a Universe off of ECC tables and converted the date format in Universe to match the SQL server date so you can use it in the filter of a WEBI query?

[Former Member]

The object Creation Date (prd.VBRK.ERDAT) says its a string (char) already in the the universe but when I wrap a DATEADD around it - it gives me a datetime value (see image)

so I think the SQL server already thinks its a date even though it says character.   I tried to format the object in MM-DD-YY and export but putting the object I created off of prd.VBRK.EDATE next ot the actual object prd.VBRK.EDATE in my webi report gave me different results - they were like a day sometimes 2 off..  I used DATEADD(day, 0, prd.VBRK.EDATE)