Question Re: Convert Text values to Timestamp in Technology Q&A

Convert Text values to Timestamp

Former Member — Wed, 05 Sep 2018 17:46:02 GMT

Hi,

I want to convert some timestamp values which are coming from a Text file source into "Timestamp" datatype, before loading them to the target table (Oracle). The values are in the format as given in below sample, and are in text format.

2018-06-06T10:24:15.9803868+08:00

Can you please advise

Thanks,
David

Re: Convert Text values to Timestamp

former_member27665 — Fri, 07 Sep 2018 21:22:22 GMT

david.krn

Try below option.

to_date('2018-06-06T10:24:15.9803868+08:00','YYYY-MM-DDTHH24:MI:SS.FF')

Re: Convert Text values to Timestamp

rajan_burad — Sat, 08 Sep 2018 06:29:27 GMT

Hello David,

I tried replicating your scenario:

Source:

Source in BODS:

Mapping:

Logic:

I used cast function to avoid conversion warnings, you can try without cast if source records are less.

(Cast function impacts performance to some extent)

After job execution:

Output in Target database(Oracle):

I've attached the .txt file used for testing.

textvalues-timestamp.txt

Hope it helps! 🙂

Reward if useful.

Thanks,

Rajan