5 Replies Latest reply on Sep 21, 2019 11:05 AM by Nico Heinze

    How to convert nstring to timestamp

    Abhisek Mohanty Active Member

      Hi All,

      I have a flatfile which contains timestamp values like 08/30/2019 16:26:14.903716.

      I need to load this timestamp into Hana DB.

      So this column will have datatype as NSTRING in Source Definition and SQ.

      I need to convert this NSTRING into TIMESTAMP in Expression Transformation.So that I can connect this column to Target definition which is of TIMESTAMP datatype.

      How can we achieve this ?

       

      I tried 2 options in Expression Transformation, but not working.

      1) TO_DATE(START_DATE,'MM/DD/YYYY HH24:MI:SS.')

      2) TO_DATE(SUBSTR(START_DATE, 0, 10) || ' ' || SUBSTR(START_DATE, 12), 'MM/DD/YYYY HH:MI:SS')

       

      Powercenter Version - 10.2.0 Hotfix2

       

      Thanks in Advance,

      Abhisek

        • 1. Re: How to convert nstring to timestamp
          pgeorge1 Guru

          The simple one will work, but you need to add the milliseconds ('US') to the format.

           

          Example:

          TO_DATE(START_DATE,'MM/DD/YYYY HH24:MI:SS.US')

           

           

          Try this and let me know if it works.

          • 2. Re: How to convert nstring to timestamp
            Abhisek Mohanty Active Member

            Thanks George, it's working for input columns which are in 08/30/2019 16:26:14.12356 Format.

             

            Few input columns are in 08/30/2019 16:26:14 Format(without the microseconds).

            So I used TO_DATE(FIELD_NAME,'MM/DD/YYYY HH24:MI:SS.US') but not working.

            I tried using TO_DATE(FIELD_NAME,'MM/DD/YYYY HH24:MI:SS') but still didn't work.

             

            How can we process 08/30/2019 16:26:14 ?

             

            Thanks,

            Abhisek

            • 3. Re: How to convert nstring to timestamp
              Nico Heinze Guru

              Your question is not 100% clear. In the first line you mention a date format 08/30/2019 16:26:14.12356 but your last question is about format 08/30/2019 16:26:14.

              Anyway, the main point is that for each possible format you need its own TO_DATE() invocation. In other words, you need to check which format fits your current input string and then apply that format in TO_DATE(). This can be achieved for example this way:

               

              Decode( TRUE,

              Is_Date( START_DATE, 'MM/DD/YYYY HH24:MI:SS.US'),

                    To_Date( START_DATE, 'MM/DD/YYYY HH24:MI:SS.US'),

              Is_Date( START_DATE, 'MM/DD/YYYY HH24:MI:SS'),

                    To_Date( START_DATE, 'MM/DD/YYYY HH24:MI:SS'),

              ...

               

              NULL)

               

              Does that answer your question?

               

              Regards,

              Nico

              • 4. Re: How to convert nstring to timestamp
                Abhisek Mohanty Active Member

                Hi Nico,

                Sorry for the confusion.

                 

                Basically I have 2 fields in my Flatfiles.

                1) In 08/30/2019 16:26:14.903716  Format(With Micoseconds) ---> This has been taken care after following George's solution that is TO_DATE(FIELD_NAME,'MM/DD/YYYY HH24:MI:SS.US').

                 

                2) In 08/30/2019 16:26:14 Format(Without Microseconds) ---> I also need to take care this one.So I tried the following 2 options but they are not working.

                Option A -> TO_DATE(FIELD_NAME,'MM/DD/YYYY HH24:MI:SS.US')

                Option B  -> TO_DATE(FIELD_NAME,'MM/DD/YYYY HH24:MI:SS')

                 

                Need your advice in this case.

                I hope I am clear this time.

                 

                Thanks,

                Abhisek

                • 5. Re: How to convert nstring to timestamp
                  Nico Heinze Guru

                  Hi Abhishek,

                   

                  yes, thanks for clarifying, so the missing 6th digit was just a typo.

                   

                  That your second format doesn't work seems strange to me. The format 'MM/DD/YYYY HH24:MI:SS') looks absolutely correct to me for the second format.

                   

                  Believe it or not, it MAY be that you have accidently entered some control character (an invisible special character) into the format string. There have been similar cases in the past, that can happen (in particular when marking some text which you have found e.g. on this forum and then copying it over to the Expression Editor window of PowerCenter).

                  There is one remedy for this (just no safe way to check whether this is the real culprit): edit the expression term which doesn't work; mark the whole text and delete it completely; and now key in (key stroke for key stroke) the whole expression term from scratch. Sometimes this does help.

                   

                  As you need to cater only for two different formats, the solution above should work fine. Try to copy and use the following term :

                   

                  Decode( TRUE,

                    Is_Date( FIELD_NAME, 'MM/DD/YYYY HH24:MI:SS.US'),

                        To_Date( FIELD_NAME, 'MM/DD/YYYY HH24:MI:SS.US'),

                    Is_Date( FIELD_NAME, 'MM/DD/YYYY HH24:MI:SS'),

                        To_Date( FIELD_NAME, 'MM/DD/YYYY HH24:MI:SS'),

                  Abort(' SEVERE ERROR: value ' || FIELD_NAME || ' does not resemble a recognised date/time format, process will be stopped now.')

                  )

                   

                  Regards,

                  Nico