9 Replies Latest reply: Jul 9, 2010 1:38 PM by s j RSS

    Problem with SAP SQLConnector and DateTime field

      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

       

        • Problem with SAP SQLConnector and DateTime field
          Karl 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.

            • Problem with SAP SQLConnector and DateTime field

              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;

               

               



               



            • Problem with SAP SQLConnector and DateTime field
              s j

              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.