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

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)

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

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

Could you please provide an example?

Thanks,

Nico

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

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