4 Replies Latest reply: Aug 1, 2016 7:29 AM by Koos Snoeks RSS

    SAP Timestamp year issue

    Koos Snoeks

      Dear QlikViewers,

       

      I'm extracting a SAP table (COSP) into my QV application, but I'm having issues with the timestamp field.

      It looks something like this: e.g.

      27-Jun-2232681748 00:00:00  (value: 7090287940000 from SAP DB)  and

      26-Sep-2232704445 00:00:00  (value: 7090296230000 from SAP DB)

       

      As you can see the year format is not what I would expect.

       

      Does anyone have any idea how to solve this? Or how to deal with it?

       

      We're using the QV SAP connector.

        • Re: SAP Timestamp year issue
          Stefan Wühl

          Try maybe

           

          =Timestamp(MakeDate(1990) + YOURTIMESTAMPFIELD / (10000 *24*60*60) )

            • Re: SAP Timestamp year issue
              Koos Snoeks

              Thanks Swuehl!

               

              This seems to do the trick. However, this only works in a listbox or in the report application itself.

               

              Is there a way so I could get rid of this problem in my transformation step/application? (And don't have to bother about it in all my further applications)

                • Re: SAP Timestamp year issue
                  Stefan Wühl

                  Does this not work also in a QV script LOAD statement, like

                   

                  LOAD

                       YOURTIMESTAMPFIELD,

                       Timestamp(MakeDate(1990) + YOURTIMESTAMPFIELD / (10000 *24*60*60) ) as NEWFIELD,

                       ...

                   

                  ??

                   

                  You can probably also do the transformation on DB side, depending on your DBMS.

                    • Re: SAP Timestamp year issue
                      Koos Snoeks

                      Thanks again Stefan!

                       

                      Your solution also works in the script... Monday morning... didn't look close enough/paid attention to it...

                      Sorry!

                       

                      I don't think I can change this field in the SAP DB (is this what you mean?). I'll do it in my QV app.

                       

                      Is this date/timestamp issue a known 'problem' with SAP DB by the way?

                      I'm new to SAP (and getting pretty frustrated with the 'thousands' of tables with unreadable abbreviations).

                      Does SAP provide some sort of overview or documentation of their tables?