CODEWITHSUNDEEP
azuretimestampazure-data-factory
SHIBASHISH TRIPATHY
SHIBASHISH TRIPATHY

Reputation: 91

How to Convert a column having one timestamp to another timestamp in Azure Data Factory

I have column ABC where timestamp is of format dd/MM/yyyy HH:mm:SS (11/04/2020 1:17:40).I want to create another column ABC_NEW with same data as old column but with different timestamp 'yyyy-MM-dd HH:mm:SS'.I tried doing in azure data factory derived column using

toTimestamp(column_name,'yyyy-MM-dd HH:mm:SS') but it did not work it is coming as NULL. Can anyone help?

Upvotes: 1

Views: 5848

Answers (3)

Palash Mondal
Palash Mondal

Reputation: 538

This is a trick which was a blocker for me, but try this-

  1. Go to sink
  2. Mapping
  3. Click on output format
  4. Select the data format or time format you prefer to store the data into the sink.

Upvotes: 0

Joseph Xu
Joseph Xu

Reputation: 6043

Data Factory doesn't support date format 'dd/mm/yyyy', we can not convert it to 'YYYY-MM-DD' directly.
I use DerivedColumn to generate a new column ABC_NEW from origin column DateTime and enter the expression bellow：

toTimestamp(concat(split(substring(DateTime,1, 10), '/')[3], '-',split(substring(DateTime,1, 10), '/')[2],'-',split(substring(DateTime,1, 10), '/')[1],substring(DateTime,11, length(DateTime))))

enter image description here

The result shows:

enter image description here

Upvotes: 2

Mark Kromer MSFT
Mark Kromer MSFT

Reputation: 3838

It's a 2-step process. You first need to tell ADF what each field in your timestamp column represents, then you can use string conversions to manipulate that timestamp into the output string as you like:

toString(toTimestamp('11/04/2020 1:17:40','MM/dd/yyyy HH:mm:ss'),'yyyy-MM-dd HH:mm:SS')

Upvotes: 4

Related Questions