6 Replies Latest reply on Apr 8, 2021 11:39 PM by Lekha G M

    extract date and time from timestamp

    J Dos Active Member

      How do I extract date and time values from timestamp and store them into separate columns?
      Thanks!

      I tried TO_DATE(TO_CHAR(TIMESTAMP_VALUE,'YYYY/DD/MM') but that doesn't work....

        • 1. Re: extract date and time from timestamp
          Arya Nymeria Active Member

          select systimestamp,trunc(systimestamp)from dual; this should give you the date. For time, can you do a substr and convert to date?

          • 2. Re: extract date and time from timestamp
            Syed Aziz Guru

            Hello J,

             

            You can create an expression transformation in a mapping with a port using SYSTIMESTAMP('NS')

             

            The value will be written to the output file with the SYSTIMESTAMP function with precision to the milliseconds (MS) and microseconds (US).

             

            If SYSTIMESTAMP function does not get precision to the nanoseconds (NS), you can use TO_CHAR function (shown below) for getting the precision to the microseconds (US)

            systimestamp.JPG

            If you need nanoseconds (NS) for time-based reasons, then you could use other means (Database, third party tools, etc.).

            If the requirement is to get a unique number, you could use either the other means mentioned or a randomizing function.

             

            Best regards,

            Syed

            • 3. Re: extract date and time from timestamp
              Nico Heinze Guru

              How exactly do you want to store data and time separately? In what data types?

              Could you please provide an example?

               

              Thanks,

              Nico

              • 4. Re: extract date and time from timestamp
                J Dos Active Member

                I need to store date in date/time field and time in char field.


                I did what Arya recommended- trunc to get a date and did to_char on timestamp and substr on time part. That seems to be working.

                 

                THanks!

                • 5. Re: extract date and time from timestamp
                  Lekha G M New Member

                  Hello,

                   

                  • If you have a relational target, then you can simply load the TIMESTAMP_VALUE to your target date column without the need of any function/conversion, given that you have defined the datatype of date column as DATE( datatype) in target table . This gives you only the date part .
                  • Time with char datatype can be achieved using TO_CHAR function TO_CHAR(TIMESTAMP_VALUE,'HH24:MI:SS')

                   

                   

                  Thanks,

                  Lekha G M

                  • 6. Re: extract date and time from timestamp
                    Nico Heinze Guru

                    That works, that's right, but in PowerCenter that can be done easier.

                    In order to get the date part into a Datetime port, the following works fine:

                      Trunc( inputdate, 'DD')

                    This will set the time portion of the receiving port to 00:00:00 and leave the date part intact.

                    For the time part (assuming you need the format HH24:MI:SS), you can use this expression for some string port:

                      To_Char( inputdate, 'HH24:MI:SS')

                     

                    Does that deliver what you need?

                     

                    Regards,

                    Nico