How to Convert a date column from UTC to PST in COPY activity of ADF?

Question

Hello Team,

I have created one column on the fly in the **"Source" ,**in the copy activity.Now the date is appearing in UTC format.

But i need to display in PST Time.So i am trying to convert from UTC to PST:

@convertTimeZone(utcnow(), 'UTC', 'Pacific Standard Time')

I am getting error:The function convertFromUtc is currently not supported for schema operations.

Please advise

Regards

RK

Answer 1

The convertTimeZone() function you're trying to use is indeed part of the expression language used in data flows or for setting up dynamic content in various ADF activities and it's not supported directly within the schema mapping or transformation settings of a Copy activity.

So instead of doing it directly in the Copy activity, you can use a Data Flow activity.

1. Add a new Data Flow resource to your ADF pipeline.
2. Add a Source transformation that connects to your original data source.
3. Use a Derived Column transformation to add or modify a column with the date/time conversion. You would use an expression similar to what you're attempting, but adapted for Data Flow's expression syntax.
4. Finally, direct the transformed data to your desired destination (Sink).

The expression in Data Flow for converting time zones might look something like toTimestamp(toDate([YourUTCColumn]), 'UTC') - 8 hours, adjusting for PST. Note that PST is UTC-8, but keep in mind Daylight Saving Time adjustments if you're targeting Pacific Time generally.

Answer 2

@Rohit Kulkarni ,

Glad that you figured out the way to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

convertimezone() function helped to convert from UTC to PST using ADF pipeline . Here is the screenshot of the solution :

Kindly accept the answer by clicking on Accept answer button so that the solution reaches the community in case anyone is facing similar issue. Thankyou.