Problem with SAP SQLConnector and DateTime field

[#1]

Hello,

Can someone help me!

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 Qlikview code I am using is:

[CRMD_TM_IACT]:

SQL SELECT
LOCK_STATE,
LOCK_OWNER,
ATTEMPTS,
HARDWARE_OUTCOME,
CALL_TIME,
RESCHEDULED,
COMPLETED_ON,
CL_PID
FROM CRMD_TM_IACT;

The quesiton I have is there any syntax I can use in this select statement to format these fields as they are read in via the statement?

I have tried a number of standard MS SQL syntax options, the SAP SQLConnector does not like them!

Thank you,

Tim

[pover]

Tim,

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.

Regards.

[#3]

Karl,

Thank you so much for the quick reply.

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!

Thanks again

Tim

[CRMD_TM_IACT]:

LOAD

COMPLETED_ON

-99999999 as

A1;

SQL

SELECT

COMPLETED_ON

FROM

CRMD_TM_IACT;

[pover]

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.

Regards.

[#5]

Thanks again Karl and yes I will post the answer if ever I am able to find it!

Regards

Tim

[disqr_rm]

Never seen such problem, but on the other hand I am not familiar with how it is stored in CRM database. If I assume it's all like R/3 or BW style, it should be working just fine.

See i you can change format of the field in QV document under "Number" tab and setting it to Date or Timestamp.

Otherwise, I recommend opening a support call to QlikView.

[#7]

Thank you Rakesh.

I have tried the number format option in QV and that does not work as the base numbers in the data are definately single digit.

It must be an issue that the SAPConnector cannot handle these types of data fields from CRM.

I will open a support ticket with QV and see what they say.

I'll post any response I get on here!

Thanks for your help and quick reply.

Tim

[Lars_Wahlstedt]

Hi,

this looks like a bug we had previously, guess you are not running the latest version.

Try upgrading to 5.40 SR1 and I am pretty sure the problem will be solved.

Regards,

Lars Wahlstedt

[bismart]

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.

[suniljain]

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.