Convert Text values to Timestamp

[Former Member]

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

[former_member27665 · Accepted solution]

david.krn

Try below option.

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

[rajan_burad]

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

textvalues-timestamp.txt