I am having problems getting the correct value for the Datetime fields in SAP CRM using Qlikview 8.5 and the SAP SQLConnector.
It is an MS SQL server database and I am trying to select records from the CRM Call List table CRMD_TM_IACT
The problem is that two fields I am selecting CALL_TIME & COMPLETED_ON are Datetime stamp field and when I look at the data in SAP, the values I see returned from the SQL SELECT satetement are single digit numbers.
The QV SQL Connector syntax is independent of the database where SAP is installed so no MS SQL syntax is going to work. Have you tried using the a function in the load statement to format the date? We is the number you are seeing exactly in the date field?
I haven't extracted data from the CRM module, but dates fields from the FI, SD, MM modules don't need additional formating. However in RH I did have to subtract the number in the date field by the number 99999999 get a normal date format.
I have tried variuous format functions inthe load statement, but nothing works as the source data in the field comming from the SQL SELECT statement is a single digit. The exact values I am seeing is the number 4 the number 5 and the number 6. We started loging calls in March, so I believe these numbers are the month numbers in this DateTime field i.e. the month is the fist number in the field (US Date format).
I have just tired this statement below (as I am desperate), but it returned:
-99999995 -99999994 -99999993
I have also performed lots of SQL extracts from date field in SD, FI, PP etc and all these have been fine. This seems to be a problem with the CRM datetime fields!
Any further sugestions would be grately apprecyiated!
I found out about the 99999999 - date format in a ABAP forum by looking in Google. You've probably already been doing this, but I can tell you that sometimes it takes sifting through alot of forums until you find the answer.
If you find it please post the answer. I expect I'll have the same problem some time in the future.
Why not leave the SELECT statement without any formatting and load data directly from SAP to a temporary table. Then format the data from a resident load of the temp table. Then delete the temp table. This will reduce any performance issues with regard to the extract and also allow you use Qlikview functions without relying on version of connector.
Some SAP Table Like COEP , Store Time stamp in Numeric String. It is in more than 8 digit. it is in nothing but is no of second start for 1999 or 1899. pls take SAP help . so you have to convert that numeric string in Date time by build logic which get date and Time from no. of second.