ADF: How to Convert a datetime column (AM/PM) to UTC format?-CodePudding

I have a column where timestamp is 5/23/2022 8:45:34 PM. I want to create a new column with same data as old column but in UTC format 'yyyy-MM-dd HH:mm:ss' and this new datetime format is 7 hours behind UTC (UTC-7)

I tried doing in azure data factory derived column using toTimestamp before it converts to UTC but it always fail.

toTimestamp(column_name,'yyyy-MM-dd HH:mm:SS')

but it did not work and the result always NULL.

Can anyone help this data conversion to UTC ?

CodePudding user response：

The reason you are getting null values for the newly added columns is because the format you specified in the toTimestamp() function is incorrect. The following is the sample data that I used to reproduce this issue. The date column here is of type String.

While using Derived column in the dataflow to create a new date column of timestamp type, write toTimestamp(date, 'MM/dd/yyyy hh:mm:ss a', 'UTC') expression as the value for this new column. Here, date is the column you want to convert to new date column and MM/dd/yyyy hh:mm:ss a is the format of the values in date column (a represents the AM/PM). You can also pass time zone value like UTC, GMT etc. which is optional.

The following is what the result looks like, with a new column of timestamp type. You can use this resulting data to perform further conversions in dataflow.