4 Replies Latest reply on Mar 14, 2021 1:26 AM by Francesco Vitelli

    Date transformation

    Francesco Vitelli New Member

      Hi, i have to tranform in a format SAP compatible a date coming from a flat file by informatica ics. the target is the update of a sap table. The original value is a string formatted as YYYY-MM-DD and i have to change it as DD.MM.YYYY

      In expression step i have created a field named data_doc_sap with type string and composed an expression like the following:

      TO_CHAR(TO_DATE(data_doc_sup,'YYYY-MM-DD'),'DD.MM.YYYY')

      In the expression preview i see the date correctly formatted but during the run the process shows a warning with no table update.

      Have you some suggestion to help me to find a working well expression?

       

      Thanks a lot,

      Francesco

        • 1. Re: Date transformation
          Nico Heinze Guru

          If the data type of this attribute in SAP is a DATE or something similar, then simply convert the input string to a Date/Time value (using the TO_DATE() function) and forward this Date/Time port to the SAP table target.

          Otherwise please explain what data type this attribute has in SAP.

           

          Regards,

          Nico

          • 2. Re: Date transformation
            Francesco Vitelli New Member

            Hi Nico,

             

            thanks a lot for ypur support, i have solved using the following expression:

             

            To_Date(substr(data_doc,9,2)||'-'||substr(data_doc,6,2)||'-'||substr(data_doc,1,4), 'DD/MM/YYYY')

             

            Where data_doc is a date coming from a flat file with format as 2021-02-28

            Now i'm able to post date into a SAP table with the correct format -> 28.02.2021

             

            But when the date value from file is NULL (and it can happen) the expression fails producing a warning without any posting in table.

             

            Do you know how can i overtake this issue? Have I to use IIF expression and if yes how can do it?

             

            Thanks so much!!

             

            Francesco

            • 3. Re: Date transformation
              Nico Heinze Guru

              As far as I understand it, that depends on how this particular date value is set up within SAP. Maybe this field must not be NULL? In this case you will have to provide a "default" value (your SAP people should be able to tell you which one), and that can be done as follows (assuming for this example that the default date is Dec. 31st, 2999):

               

              IIF( IsNull( DATA_DOC),

                TO_DATE( '29991231', 'YYYYMMDD'),

                TO_DATE( DATA_DOC', 'YYYY-MM-DD')

              )

               

              If the field is allowed to be NULL, then a NULL value from the source should be forwarded to the target as a NULL value. But you can make double-sure that this works using the following expression:

               

              IIF( IsNull( DATA_DOC) OR Length( RTrim( DATA_DOC)) = 0,

                NULL,

                TO_DATE( DATA_DOC, 'YYYY-MM-DD')

              )

               

              Granted, this looks like overkill, and that's true: I've inserted two special cases here, namely that the source fields is NULL and that it consists of blanks only (instead of carrying a valid date). I'm just cautious.

               

              Regards,

              Nico

              1 of 1 people found this helpful
              • 4. Re: Date transformation
                Francesco Vitelli New Member

                Thanks a lot Nico! It works changing as you have suggested.